DATEDIF

The DATEDIF function returns the number of days, months, or years between two dates.

DATEDIF(start-date, end-date, calc-method)

start-date: The starting date. start-date is a date/time value (the time portion is ignored) or date string value.

end-date: The ending date. end-date is a date/time value (the time portion is ignored) or date string value.

calc-method: A modal value that specifies how to express the time difference and how dates in different years or months are handled.

"D": Return the number of days between the start and end dates.

"M": Return the number of months between the start and end dates.

"Y": Return the number of years between the start and end dates.

"MD": Return the days between the start and end dates, ignoring months and years. The month in end-date is considered to be the month in start-date. If the starting day is after the ending day, the count starts from the ending day as if it were in the preceding month. The year of the end-date is used to check for a leap year.

"YM": Return the number of whole months between the start and end dates, ignoring the year. If the starting month/day is before the ending month/day, the dates are treated as though they are in the same year. If the starting month/day is after the ending month/day, the dates are treated as though they are in consecutive years.

"YD": Return the number of days between the start and end dates, ignoring the year. If the starting month/day is before the ending month/day, the dates are treated as though they are in the same year. If the starting month/day is after the ending month/day, the dates are treated as though they are in consecutive years.

Examples
If A1 contains the date/time value 4/6/1988 and A2 contains the date/time value 10/30/2006: =DATEDIF(A1,A2,"D") returns 6,781, the number of days between April 6, 1988, and October 30, 2006. =DATEDIF(A1,A2,"M") returns 222, the number of whole months between April 6, 1988, and October 30, 2006. =DATEDIF(A1,A2,"Y") returns 18, the number of whole years between April 6, 1988, and October 30, 2006. =DATEDIF(A1,A2,"MD") returns 24, the number of days between the sixth day of a month and the thirtieth day of the same month. =DATEDIF(A1,A2,"YM") returns 6, the number of months between April and the following October in any year. =DATEDIF(A1,A2,"YD") returns 207, the number of days between April 6 and the following October 30 in any year. =DATEDIF("04/06/1988",NOW(),"Y")& " years, " &DATEDIF("04/06/1988",NOW(),"YM")& " months, and " &DATEDIF("04/06/1988",NOW(),"MD")& " days" returns 25 years, 2 months, and 25 days, the current age of someone born on April 6, 1988 if today is July 1, 2013.

Examples

If A1 contains the date/time value 4/6/1988 and A2 contains the date/time value 10/30/2006:

=DATEDIF(A1,A2,"D") returns 6,781, the number of days between April 6, 1988, and October 30, 2006.

=DATEDIF(A1,A2,"M") returns 222, the number of whole months between April 6, 1988, and October 30, 2006.

=DATEDIF(A1,A2,"Y") returns 18, the number of whole years between April 6, 1988, and October 30, 2006.

=DATEDIF(A1,A2,"MD") returns 24, the number of days between the sixth day of a month and the thirtieth day of the same month.

=DATEDIF(A1,A2,"YM") returns 6, the number of months between April and the following October in any year.

=DATEDIF(A1,A2,"YD") returns 207, the number of days between April 6 and the following October 30 in any year.

=DATEDIF("04/06/1988",NOW(),"Y")& " years, " &DATEDIF("04/06/1988",NOW(),"YM")& " months, and " &DATEDIF("04/06/1988",NOW(),"MD")& " days" returns 25 years, 2 months, and 25 days, the current age of someone born on April 6, 1988 if today is July 1, 2013.

See alsoDAYS360 NETWORKDAYS YEARFRAC

Helpful?

Formulas and Functions Help

DATEDIF