DATEDIF & TODAY

Bringing more ease and efficiency to your Excel work is always a worthy goal. How Excel handles your typical numeric data is for the most part, quite intuitive. Dates, however, can be a bit troublesome at times.

As most seasoned Excel users know, 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!

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

The Syntax of the Function is as Follows:

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

Where the Time Interval is expressed as follows (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

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

=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 gem in mind, you may find great many ways to use the DATEDIF function in the future. Curious that it is not documented, isn’t it?...