How to Calculate Excel Date Difference Using DATEDIF() Function?

We often use Excel on a regular basis. It’s a great tool and has so many useful features. Many frequent users decide to undergo excel training in Denver so that they know exactly how to use it because some of the features can be quite tricky to understand and there are a few ways to complete tasks. There are quite a few handy ways to figure out the Excel date difference. You can either use a function provided by Microsoft or even subtract dates in excel to figure out how many years, months or days gap is there between them. However, I’ve found the Excel DATEDIF() function to be quite helpful.

Before going further, if this is the first time that you’re hearing about the Excel dates and how to manipulate them, then I’d suggest you to first go ahead and read the article about Excel Date Function. This article discusses the details of how the Excel Date function actually works, what are the date serial numbers, how the Excel keeps tracks of the dates and other important function that will help you get along with this excel function.

So I reckon that the article that I’m referring to will be a great resource to start with.

 

How to Calculate the Difference Between Two Dates in Excel?

As said above, there are multiple ways to work this out. Excel itself provides a function using which, it becomes quite easy to figure out the difference between two dates. However, at the same time, you can achieve the same objective by subtracting dates in Excel which is beyond the scope of this article. Let’s keep for some other day.

 

Excel DATEDIF() Function

The Excel DATEDIF() (pronounced, Date Diff) function is quite a unique one. No, I don’t mean its functionality but the treatment it receives from Excel.

It appears like that the Microsoft is not so fond of it as the DATEDIF() function is not present in the Excel’s standard functions library.

You can observe this when typing this formula in any cell. Usually, when you start typing a proper formula, a small drop-down box shows up which assists you to choose your function from the list. This does not happen with the Excel’s DATEDIF() function because Excel hasn’t provided documentation of this function within its library. However, the Microsoft’s website does tell you about this function in detail.

 

Anyways, let’s see how this formula work. The standard syntax for DATEDIF() function is:

=DATEDIF(start_date, end_date, unit)

 

The “unit” argument decides if you want to see the difference in days, months or years etc.

There are a bunch of units that you can use in the DATEDIF() function. See the image below:

 

Though the image itself is quite explanatory but there are two units that I’d want to explain especially.

These are the “YM” and “MD” units

 

DATEDIF() “YM” and “MD” Units

The units, “YM” and “MD” return the date difference in months and days respectively.

But that’s what the “M” and “D” units do as well. Right? So what’s the difference?

Well, the difference is that the “M” and “D” units return the total difference in months and days. Whereas, “YM” ignores the year and returns the difference in months only and “MD” ignores the months and returns the difference in days only.

Does it sound confusing? Don’t worry. We’ll solve an example.

 

Let’s say we have two dates. The start and the end dates. These are 09-Nov-1995 and 10-Dec-1999

 

If I ask you, what is the difference between these two dates in terms of total months, your answer should be 49 months. That’s correct. The gap between these two dates is 49 months and using “M” as our unit will produce this result.

=DATEDIF("09-Nov-1995", "10-Dec-1999", "M")
//Output 49

 

What if we use the “YM” units instead of “M”?

=DATEDIF("09-Nov-1995", "10-Dec-1999", "YM")
//Output 1

 

So when we use YM, the output is 1. Reason? Excel has ignored the year and only considers the month (and date) and since there is a gap of one full month between 9-Nov and 10-Dec, the output is 1.

Similarly, we can recreate this example but this time with the unit, “MD”.

When we use “D” as a unit, the output would be:

=DATEDIF("09-Nov-1995", "10-Dec-1999", "D")
//Output 1487

 

This means that between the start and end dates, there’s a gap of 1,492 days. What if we use “MD” as our unit?

=DATEDIF("09-Nov-1995", "10-Dec-1999", "MD")
//Output 1

 

Again, Excel has ignored the year and the month so the only gap left is of 1 day (between 9 and 10). Hence, out output.

One interesting point to note is that what happens when the ending date is less than the start date? I’m not talking about month and year but only the date. What if we have used the ending date as say, 4-Dec-1999. What would have “MD” generated?

=DATEDIF("09-Nov-1995", "04-Dec-1999", "MD")
//Output 25

 

Get it? The reason is that though Excel has ignored the year and the month but still, it calculates the date from the start date and keeps going till the next 30-31 days. SO starting from 9, Excel reaches 4 after 25 days. Hence the output.

I hope that helps clears the confusion.

 

Data Type Used in DATEDIF() Function

You can use the following format or data type in the DATEDIF() function

• Text-based string. Which means that you may need to enclose your dates in double quotation marks (“”).
• Date’s serial number. If you have no clue what I’m talking about then I’d suggest you read this article about the Excel’s DATEVALUE() Function. You don’t need to use the double quotation marks with the serial number.
• Or you can use the result of some other formula within the DATEDIF() function.

 

Note: If the start date is greater than the end date, Excel with give a #NUM! Error.

Here’s an example of using the DATEDIF() function in different ways.

 

And here are the formulas used in the above example.

 

 

How to Calculate Age from Date of Birth in Excel?

Once we know how the DATEDIF() function works it’s easy to work out the difference between two dates in Excel. The other couple of things you need to know (other than using the DATEDIF() function of course) is how to concatenate strings and how to generate the current date in Excel.

We’ll solve both problems below. First, let’s start with the current (today’s date).

 

Excel Formula for Today’s Date

Figuring out today’s (or current) date is quite simple in Excel. The formula used is:

=TODAY()

That’s it. This formula returns the current date formatted as a date.

 

Second, in order to concatenate strings and formula within a single cell in Excel, you need to start the cell with an is equal to (=) sign and use the ampersand (&) symbol to combine different data types together.

 

Let’s solve a very quick example to understand what I’m talking about.

Say, a guy born on 9th November, 1995 wants to know his age accurate to the number of days.

Since we know about the DATEDIF() and TODAY() functions in excel, we can easily calculate the age of this person.

Here’s how we will do it.

 

One important point to notice in cells D2 and E2 is that we’ve used the units “YM” and “MD” instead of just “M” and “D” respectively. The reason behind this is that units “M” and “D” would have calculated the total numbers of months and days respectively from the start date.

This is not what we want.

The reason is quite logical. When you’re 20 years and 8 months old, you do not say that your age is ((12 x 20) + 8) 248 months. You convert it into years and this is what we’ve done in our example.

 

Cell A5 combines all the results and tells us in plain language how old the person in the example is. Cell A6 shows the formula to generate this result.

• Anything as a string has been enclosed in double quotation marks (“”).
• C2, D2 and E2 are actually referring to the output in the respective cells.
• The symbol, ampersand (&) is combining the string and the cell references.
• Note that we’ve started with the is equal to sign (=). This way, Excel knows that it needs to interpret the content within instead of regurgitating the output as is.

 

Final Word

Though I’ll wrap up my article here but excel dates are not over yet. There are still tons of things that you can do with Microsoft Excel Dates. However, I’m sure that after reading this article, you won’t be forgetting some important dates in your life.