Ever wondered why calculating dates in your head is so difficult? It feels like one of those things that should be easy, but seven-day weeks, months with uneven numbers of days, and occasional leap years all conspire to wreak havoc with your math ability.

Don't believe us? How many days are there between March 17, 2021 and May 20, 2023? If you can figure it out in under five seconds, then okay, you're clearly a genius. But if it takes you longer than that, then count yourself normal—and stop struggling when Excel can do the hard work for you.

The DATEDIF Function in Excel

DATEDIF is an Excel function tailored for this exact task. It can calculate the number of days, months, or years between two dates. The syntax for it is as follows:

=DATEDIF(start_date, end_date, unit)

DATEDIF takes in three arguments:

• start_date is the earlier date in the calculation.
• end_date is the later date in the calculation.
• unit is a text string that specifies the unit of time that you want to calculate (e.g., years, months, days).

If you put these all together, the following formula returns the difference between the two dates A1 and B1 in days:

=DATEDIF(A1,B1,"D")

The start_date should, naturally, be chronologically earlier than the end_date. If the first date in the function is later than the second, DATEDIF will return an error.

The third argument in DATEDIF determines the unit of the date difference. This can be simple units such as Y, M, and D, which return the difference in years, months, and days respectively. However, the units can also be compound units such as MD, YM, and YD.

The syntax and usage of DATEDIF are inherently simple. The only thing you need to master with this function is understanding the units. With that, you'll be able to choose the best unit for your scenario. Let's go through this function with some hands-on examples.

Calculating the Difference Between Two Dates in Days, Weeks, Months, or Years

The simplest usage of DATEDIF is to calculate the difference between two dates in simple units. D, M, and Y return the difference in whole days, months, and years. Since every week is seven days, you can divide the difference in days by seven to get the difference in weeks.

Unlike D, M, and Y, calculating the difference in weeks by dividing the days by seven won't give the count of whole weeks. In fact, it'll likely return decimals.

Take a look at the spreadsheet below. In this spreadsheet, we've got two dates approximately three years apart. You can use the DATEDIF function to calculate the exact difference between the two dates in different units.

Before getting started, make sure that the cells containing date values are in date format. This way, Excel knows that it's dealing with date values. Conversely, ensure that the output cells are in number formats, and not date formats.

1. Select the cell where you want to display the calculation results.
2. In the formula bar, enter the formula below:

   DATEDIF($A$2, $B$2, "D")

3. Press Enter.

The formula will calculate the difference between the dates in cell A1 and B2, and then output the results in days. The absolute references ($) ensure that once you autofill the formula into the cells below, A2 and B2 stay constant.

Once you populate the other cells, all you'll need to do is to change the unit. To calculate the difference in weeks, you can divide the days formula by seven:

DATEDIF($A$2, $B$2, "D")/7

As discussed before, this formula won't return the whole weeks between the dates. However, you can remove the decimals through custom formatting in Excel to get whole numbers only.

So far, so good, right? The three remaining units (YD, YM, and MD) are a bit more sophisticated, but nothing you can't figure out with a simple example.

The second letter in these units determines the output's unit, and the first letter indicates the omitted units. So, for instance, YD counts the days between the two dates while omitting the year difference.

The spreadsheet above demonstrates this point. YD assumes that the dates are in the same year (Y) and counts the difference in days (D). MD assumes that the two dates are in the same year and month (M) and counts the difference in days (D). YM assumes that the dates are in the same year (Y) and counts the difference in months (M).

Calculating the Cumulative Time Between Two Dates

By now, you know how to calculate total time periods, but what if you want to work out the total amount of time elapsed, listed as days, months, and years? Now that you know how to use the YD, YM, and MD units, this is only a matter of combing the equations into a single cell.

1. Select the cell where you want to display the results.
2. In the formula bar, enter the formula below:

   =DATEDIF(A2,B2,"Y")&" Years "&DATEDIF(A2,B2,"YM")&" Months "&DATEDIF(A2,B2,"MD")&" Days."

3. Press Enter.

You should now see a nice output telling you the exact time that elapsed between the two dates. The formula here consists of three instances of DATEDIF.

The first instance uses the Y unit to simply output the year difference. Next, since the year difference has been stated, DATEDIF uses the YM unit to omit the years and output the months difference. Finally, both the year and months difference are omitted, and the days difference is stated through the MD unit.

The ampersand (&) sign states a new string in the formula. You can use this symbol to combine different functions in a single formula. Note that you won't need to put an equal sign (=) before every function. The first one is enough.

When writing long formulas like this, you can add a line break in the formula bar with Alt + Enter to better distinguish the functions. And if you feel like you're going to be using this formula a lot, you can save yourself a lot of time by turning it into a custom function using LAMBDA in Excel.

Track Time and Date With DATEDIF in Excel

Dates are numbers, after all. That means you can safely rely on Excel to calculate the difference between dates for you. DATEDIF is an Excel function specifically cut out for this task, and now you know how to use it.

You can use DATEDIF to compare dates, find out how old something is, or how long you've got until a deadline. Since DATEDIF outputs integers, you can also combine DATEDIF with other functions and formulas to create more complex calculations. One more tool in your productivity shed!