The Curious DATEDIF

The marvelous holiday of St. Patrick’s Day is an excellent time to revisit the Curious function of DATEDIF. For most of us, (Irish or not), Excel handles our typical numeric data in an intuitive manner. Dates, however, can be a bit worrisome at times.

If you have been working with Excel for some time, I am sure you know that the way Excel handles dates can be a bit “Curious” at times. Finding the Difference between two Dates, for instance, is not readily intuitive, so for this day of green, we are going to look at the totally cool DATEDIF function!

Curious Note
One small curiosity about DATEDIF is the fact that it is not a “documented” function in Excel. Not in Excel 2007, 2010, 2013, or 2016. You cannot, for example, go to the Insert Function wizard and find it in any of the lists.

The Syntax of the DATEDIF Function is Entirely Simple:

=DATEDIF(“First Date”, “Second Date”, “Time Interval”)

Where the Time Interval is expressed as follows (Important Note: Unless referring to cell values for the dates, all arguments must be enclosed in Quotation Marks):

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

An interesting and rather fun way to apply this function is to nest the TODAY() function into it and calculate a Person’s Age. The TODAY() function, of course, returns the Current Date, and when used with DATEDIF, it can produce an Excel calculator that you may find, well, curiously amusing.

=DATEDIF(BirthDate, TODAY(), “y”)

Another interesting way to use DATEDIF and TODAY is to make a dynamic Day Number Calculator. The elapsed number of days in the current year can be determined in a Single Cell with the formula:

=DATEDIF(“1/1/2014”, TODAY(), “d”)

By keeping this little hidden Green Gem in mind, you may find great many ways to use the DATEDIF function in the future. It really is Curious that it isn’t formally documented. Happy St. Patrick’s Day, All!