## XL: Formula to Calculate Number of Years Between Dates |

The information in this article applies to:

- Microsoft Excel for the Macintosh, versions 1.x, 2.2, 3.0, 4.0

## SUMMARY

To find the number of whole years between two dates, use the following formula

=YEAR(date2)-YEAR(date1)-IF(MONTH(date2)<MONTH(date1),1, IF(MONTH(date2)=MONTH(date1),IF(DAY(date2)<DAY(date1),1)))

where "date2" is the later date and "date1" is the earlier. For example, to find the number of whole years a person is, the formula would be

=YEAR(NOW())-YEAR(birthdate)-IF(MONTH(NOW())<MONTH(birthdate),1, IF(MONTH(NOW())=MONTH(birthdate),IF(DAY(NOW())<DAY(birthdate),1)))

where "birthdate" is a cell with the date of birth.

You can also use the following formula to calculate the number of years between a date and today:

=INT((TODAY()-A1)/365.25)

Use the following formula to calculate the number of Years and Months

=INT((TODAY()-A1)/365.25)&" Y " &INT(MOD((TODAY()-A1)/365.25,1)*12)&" M"

## MORE INFORMATION

Do not enter a date in mm/dd/yy format (as in 1/1/60) into the formula because that will be calculated as 1 divided by 1 divided by 60. To use the date in the formula without referencing a cell, use the DATE(year,month,day) function.

Additional query words: 1.0 1.00 1.03 1.04 1.06 1.5 1.50 2.20 3.0 4.00 age

Keywords :

Version :

Platform :

Issue type :

Technology :

Last Reviewed: March 12, 1999 |