DATEDIF function
DATEDIF calculates the difference between two dates and expresses the result in terms of months, days, or years.
The DATEDIF function, which isn’t documented in the Excel Help system, is one of the little Excel mysteries. Although the Excel 2000 Help system has an entry for DATEDIF, the function is not documented in earlier or later versions.
The syntax for the DATEDIF function is
=DATEDIF(StartDate,EndDate,Interval)
StartDate and EndDate are standard dates (or a reference to a cell that contains a date). The third argument, Interval, is a text string that specifies the unit of time that will be returned.
Valid interval codes are described in this list:
- m: The number of complete months between StartDate and EndDate.
- d: The number of days between StartDate and EndDate.
- y: The number of complete years between StartDate and EndDate.
- ym: The number of months between StartDate and EndDate. This interval excludes years, so it works as though the two dates are in the same year.
- yd: The number of days between StartDate and EndDate. This interval excludes years, so it works as though StartDate and EndDate are in the same year.
- md: The number of days between StartDate and EndDate. This interval excludes both month and year, so it works as though StartDate and EndDate are in the same month and the same year.
This function is majorly used for Age calculation. To calculate the current Age, you can use the below formula:
=DATEDIF(DOB,TODAY(),”y”) & ” years, “&DATEDIF(DOB,TODAY(),”ym”) & “months, “&DATEDIF(DOB,TODAY(),”md”) & ” days”
Where DOB is the Date of Birth. You can use a cell reference or manually write your DOB. Ex: “17-Jul-81″
This formula returns a text string, like this: 33 years, 8 months, 17 days
