Top Menu

Tag Archives | DATEDIF

Excel’s Undocumented DATEDIF Gem

Many people if they want to calculate the period between two different dates would subtract the earlier date from the later date. As Excel stores dates as numbers this normally works. But what if you want to calculate the number of months, or years?  How do you account for leap years, or 30 day months? There is a function hidden in Excel that for years has remained undocumented by Microsoft. Even now that Excel 2013 is released to the public, it remains undocumented. I am referring to the DATEDIF function, which can be used in all versions of Excel.

The DATEDIF function calculates the difference between two dates. You can specify a variety of different intervals, such as the number of years, months, or days between the dates. This function is great for calculating ages.

The syntax is:    =DATEDIF(EarlierDate, LaterDate, Interval)

Where: EarlierDate is the first date,    LaterDate is the second date,   Interval is the interval type to return.

There could be occasions when an error message is displayed by Excel. They are:

  • If EarlierDate is later than LaterDate, DATEDIF will return a #NUM! error.
  • If EarlierDate or LaterDate  are not valid dates, DATEDIF will return a #VALUE error.
  • If Interval is not one of the items listed below, DATEDIF will return a #NUM error.

The Interval value should be one of the following:

Interval

Meaning

Description

m

Months Complete calendar months between the dates

d

Days Number of days between the dates

y

Years Complete calendar years between the dates

ym

Months Excluding Years. Complete calendar months between the dates as if they were of the same year

yd

Days Excluding Years. Complete calendar days between the dates as if they were of the same year

md

Days Excluding Years And Months. Complete calendar days between the dates as if they were of the same month and same year

If you are including the Interval string directly within the formula, you must enclose it in double quotes:

=DATEDIF(EarlierDate, LaterDate, “m”)

If you have the interval in another cell referenced by the formula, that cell should not have quotes around the interval string. For example, with the formula

=DATEDIF(EarlierDate, LaterDate, A1)       cell A1 should contain m not “m”.

And finally as an example, to calculate someone’s age use the formula:

=DATEDIF(DateOfBirth, DateToday ,”y”)