What’s the Dif, Man?

Back in July of 2010, we looked at the obscure, and curiously undocumented function, DATEDIF. Microsoft, in all of its wisdom, (small amount of gentle sarcasm here), has chosen not to include documented information on this Essential Function in Excel. Over the past 5 years of this blog, this topic has been one of the readers’ favorite.

As any longtime user knows, the way Excel handles dates can be a bit puzzling at times. Finding the Difference between two Dates, for instance, is not readily intuitive. Here is where DATEDIF shines!

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

Important Note #1 (you don’t need this craziness): The Second Date must be greater than the First Date, or you will get a Number Error.

A novel application of this function is to nest the NOW() function into it and calculate a Person’s Age. The NOW() function returns the Current Date and Time, and when used with DATEDIF, it can produce an Excel calculator that many find amusing. (Note: the “BirthDate” can refer to an easily changed Cell Value):

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

Important Note #2: 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”).

You may well find a great many ways to use the DATEDIF function, which may lead you to wonder, Why is this Terrific Tool not documented? Ah, well, who am I to question the great Microsoft…