# Round to Closest Number of Years

#### BoTrudeau

##### New Member
I have searched online, but I cannot find a solution to accomplish my objective. My objective is this: I am trying to subtract one date from another, then round the result to the closest number of years. In the federal tax world, a person's age for certain tax calculations is not based on his or her last birthday, but rather, the birthday to which that person is closest. So, if I subtract the date of birth, August 9, 1946 ("Date X"), from the target date, January 23, 2009 ("DATE Y"), the result is 62 years, five months, and 14 days. Because the person is closer to her 62nd birthday on January 23, 2009, the result I am looking for is 62. When I use the formula "=YEAR(DATE Y)-YEAR(DATE X)", it gives me the result 63. (I.e., it rounds up.) I have also tried the MROUND function using a 1 multiple, and it too returns 63. Any help would be greatly appreciated!

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### platonic567

##### Board Regular
See if below formula is what you are looking for:

A1: 8-9-1946
B1: 1-23-2009

Code:
``=ROUND(((YEAR(B1)-YEAR(A1)-1)+((DATE(YEAR(A1),12,31)-A1)+(B1-DATE(YEAR(B1),1,1)))/365),0)``

The approach I used is to find the year difference and days difference separately and then return the result in decimal format.

Replies
3
Views
476
Replies
14
Views
561
Replies
9
Views
429
Replies
3
Views
284
Replies
35
Views
2K

1,191,118
Messages
5,984,749
Members
439,907
Latest member
Kayfabe

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back