XL: Formula to Calculate Number of Years Between Dates

ID: Q34319

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 :