Find the Difference Between Two Dates

I usually talk about the methods for using =YEAR(), =MONTH(), =DAY() functions, but there is a cool old function hiding in Excel. The DATEDIF function is left over from Lotus. While Excel help doesn't talk about this function, it is a great way to find the difference between two dates.

The syntax is =DATEDIF(EarlierDate,LaterDate,Code)
Here are the valid values that you can use for Code.

• Y - will tell you the number of complete years between the two dates.
• YM - will tell you the number of complete months, excluding the years, between the two dates.
• MD - will tell you the number of complete days, excluding complete months, between the two dates.
• M - will tell you the number of complete months. For example, I've been alive for 495 months
• D - will tell you the number of days. For example, I've been alive for 15,115 days. This is a trivial use, since you could just subtract one date from another and format as a number to duplicate this code.

The useful codes are the first three codes. On the show, I demonstrated this worksheet. Identical formulas in columns D, E, and F calculate the DATEDIF in years, months, and days.

The formula in column G strings this together to create text with the length of time in years, months, and days.

You could combine this into a single formula. If cell A2 contains the joining date, use the following formula in B2:
=DATEDIF($A2,TODAY(),"Y")&" years, "&DATEDIF($A2,TODAY(),"YM")&" months & "&DATEDIF($A2,TODAY(),"MD")&" days"