DateDif Revisited

A little over a year ago, I discussed the obscure DateDif function, and it was a readers’ favorite.

As I mentioned in my 2009 post, Microsoft has for reasons only they know, mysteriously chosen not to include documented information of this fascinating function in Excel. DateDif is a very useful (I think it is essential) tool for doing calculations with dates. Here is a refresher with some additional good information.

The syntax of the function is as follows:

=DateDif(First Date, Second Date, Time Interval)

Where the Time Interval is expressed as follows:

d   (Days) = Number of days between the dates
m  (Months) = Complete calendar months between the dates
y   (Years) = Complete calendar years between the dates

Note: The Second Date must be greater than the First Date, or you will get a Number Error.

An entertaining application of this function is to nest the NOW() function into it and calculate a person’s age as follows (Note: the “BirthDate” can refer to an easily changed cell value):

=DateDif(BirthDate, NOW(), “y”)

Special Note: If you put the Time Interval in the function directly, be sure to put “quotation marks” around it (e.g. “m”). If you put it into the formula via a cell reference, do not use the quotation marks (e.g. the cell should contain m, not “m”).

There are a great many ways to use the DateDif function, so give it a try some time. You may wonder how you ever lived without it!