Excel DATEDIF Function – How To Use

The DATEDIF function is categorized as a DATE/TIME function in Excel. The function name almost gives away its purpose. It calculates the difference (DIF) between two given dates (DATE). This function is particularly useful for financial analysts. For instance, when they need to calculate the holding period in terms of days, months, or years.

DATEDIF is an undocumented function, this means you would not find this function in the formula tab. In addition to this, Excel will also not help you with IntelliSense (the suggestions Excel provides while you type function syntax) while writing the DATEDIF function, but it works well when configured correctly.

Note: Please note that DATEDIF Function in Excel is totally different from the DATEDIFF (notice the extra 'F') function in VBA.

Syntax

Learning the syntax of the DATEDIF function is crucial since Excel won't help you with IntelliSense. The syntax is as follows:

Arguments:

'start_date' – This is a required argument where you will need to insert the starting date.
'end_date' – This is a required argument where you will need to insert the ending date.
'unit' – This argument is supplied as a text string and represents the units in which we want the formula to compute the difference. You may use the table given below for appropriate codes required for the desired results.

Codes for Unit Argument

Important Characteristics of the DATEDIF function

• The DATEDIF function is an undocumented compatibility function inherited from Lotus 1-2-3. When you start typing the DATEDIF function, Excel won't show any suggestions as it does for other functions.
• The DATEDIF function accepts inputs in the form of a text string, serial numbers, or relayed output from other Excel functions.
• If the date entered as start_date occurs after the end_date, the function will return a #NUM error. If either of the arguments is supplied with a date that Excel doesn't recognize, the function will return a #VALUE error.

Examples

Let's try to see some examples of the DATEDIF function.

Example 1 – Plain Vanilla Formula for the DATEDIF Function

Let's just test the waters first to see how the DATEDIF function works in its simplest form. We have two dates in cells A2 and B2, and we'll compute the difference between them using the following formula:

The formula sets the unit to "d" which means the final output will be in terms of complete days. The function will return the difference in days between the two dates supplied in the first two arguments. In our example, the difference comes to 1985 days.

Note that this computation could have been made without using the DATEDIF function. Excel stores dates as serial numbers, and subtracting one serial number from the other would effectively return the difference in the number of days.

Example 2 – Count Difference in Days (All Variations)

Alright Excel ninjas, we already discussed that there are multiple ways to obtain output from the DATEDIF function. We'll now talk about the options available for obtaining the difference in terms of days.

We saw the output that "d" gives us. Let's also take a quick look at what "yd" and "md" return when used in the DATEDIF function.

When we use "yd" in the unit argument, the difference is calculated in terms of days, but the year component is ignored. In effect, the difference will be calculated as if the start and end date occurred in the same year. Therefore, the difference, in this case, reduces to 158 from 1985.

When we use "md" in the unit argument, the difference is calculated in days, but this time around, the function will ignore both month and year components. In this case, the difference is calculated as though both dates occur in the same month and same year. Therefore, the difference further reduces to 5 from 158.

Example 2 – Count Difference in Months (All Variations)

Crack your knuckles because we have a few more ways to calculate the difference between two dates. This time around, we'll calculate the difference in terms of months.

When we use "m" in the unit argument, Excel computes the difference in months as any person would. Every 30/31 days add one month to the tally. In our example, the formula returns 65 months.

However, when we use "ym" in the unit argument, the output changes to 5. This is because now, Excel ignores the year component and only computes the difference in number of months. It's the same as saying that Excel assumes that both the start and end dates occur in the same year. Therefore, the output reduces to 5.

Cool, right?

We're not done, though…

Example 3 – Count Difference in Years

The inception that the unit argument has created ends here. There is only one way to calculate the difference in the number of years, like so:

It's rather simple. Excel just gives us the complete difference between the two years. In our example, the difference is 5.

However, note that the difference isn't exactly 5. Since the day and month for both dates are different, there has to be some fractional year that the DATEDIF function ignored. If you want a precise difference, you could use the YEARFRAC function like so:

The YEARFRAC function will give you an exact difference, which in this case, is 5.43 years.

Example 4 – Get Years, Months, and Days Between Two Dates

It's time to raise our ambitions and accomplish a little more with this nifty Excel function. This time around, we're going to calculate the difference in terms of years, months, and days together using the DATEDIF function.

We can do this with the following formula:

Don't be overwhelmed by the size of the formula, we'll punch it flat in just a moment.

There are three formulas in here, joined together as one using the concatenation operator. We've already discussed those individual formulas in the previous examples, so let's get to the logic of how this formula comes together.

The first DATEDIF calculates the difference in years. Plain and simple.

The second DATEDIF calculates the difference in months but ignores the years. This works out because we've already calculated the difference in years using the first DATEDIF.

The third DATEDIF calculates the difference in days but ignores both months and years. Again, this works since both the difference in months and years has already been calculated using the previous two DATEDIFs.

There's one more component in the formula—the text strings. They are just concatenated with the DATEDIF function's output for better presentation and don't contribute to any computations in the formula.

This is how Excel calculated the final output of 5 years, 5 months, 5 days.

Easy-peasy, isn't it?

Example 5 – Calculating Age from Date of Birth

Let's draw some inspiration from the previous formula and use it for a noble purpose—calculating the spouse's age. I got into a friendly argument with my spouse and she begs to differ on her exact age. We decided to get creative and pulled up a spreadsheet.

Calculating age in Excel is pretty simple, we just modified the previous formula slightly to get this done:

A light bulb probably already went off as you skimmed through this formula, but I'll still point it out—the only difference here is the use of TODAY function.

The TODAY function works as our end_date argument since we want to compute the difference from a certain date (the DOB) right up to today.

Cell A2, of course, contains the Date of Birth that will work as our starting point. Other than that, the formula uses the same logic as it did in the previous example.

The output, therefore, is 26 years, 3 months, 10 days. I obviously didn't use her actual DOB since I don't like sleeping on the couch.

Alright, with this, we've covered the DATEDIF function inside out. Consider yourself an expert on the DATEDIF function once you've practiced these formulas thoroughly. However, I strongly recommend against using your spouse's DOB when you practice. While you keep yourself busy with the DATEDIF function, we'll have another Excel function loaded up, ready to fire away!