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 |
