Round to Closest Number of Years

[BoTrudeau]

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!

 

[platonic567]

See if below formula is what you are looking for:

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

=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.