Excel DATEDIF Function: Find Difference Between Dates (2023)

The Excel DATEDIF function is one super useful function 😊

From the name itself: DATEDIF (Date + Difference), this function helps you find the difference between the end date and start date in days, complete months, or complete years.

This is a hidden function in Microsoft Excel πŸ”

But today? It’s not hidden anymore πŸ˜‰

Today, you’ll learn how to correctly apply the DATEDIF function in Excel so you can quickly find the difference between dates.

Find difference in days

Sometimes we need to calculate the difference in days between two dates πŸ“†

In the example below, we have the expected start date and end date of a project.

Calculate the difference between two dates in days

Now, you have to find the number of days between the two dates.

You can do it by following the steps below.

  1. Enter an equal sign and type the DATEDIF.

The syntax of the DATEDIF function is DATEDIF(start_date,end_date,unit).

Important note – Unlike the other functions, the function will not appear as you start to type this date function. So, you have to type the entire name of the function. Further, it will not show you the arguments of the function.

=DATEDIF

Enter an equal sign and type the DATEDIF
  1. Open a parenthesis and select the initial date (start date) as the first argument of the function.

Then, your DATEDIF formula is;

=DATEDIF(A2

Open a parenthesis and select the start date as the first argument
  1. Enter a comma and select the end date as the second date of the DATEDIF formula.

The updated DATEDIF formula is;

=DATEDIF(A2,B2

 Select the end date as the second argument of the DATEDIF formula
  1. Next, enter the unit. As you want the difference in days, you have to enter the letter D within quotes.

The following formula should be in the formula bar;

=DATEDIF(A2,B2,”D”

Enter the final argument - Unit - DATEDIF function
  1. Finally, close the parenthesis and press the Enter key. Then, the DATEDIF function returns the number of days between two date values.

Then, the whole formula is;

=DATEDIF(A2,B2,”D”)

Use the DATEDIF function to find date difference in days

Hence the total number of days between the start and end dates is 152. In other words, there are 152 complete days between the first and last day.

Let’s say that you need to find the difference in days ignoring years. In that case, you have to use the “YD” as the time unit instead of “D” 😍

If you need to find the difference in days ignoring months and years between two dates, you have to use “MD” as the time unit. But, this has some known issues and may give you incorrect results.

So, if you use “MD” as the time unit, you must double-check your answers.

Kasper Langmann, Microsoft Office Specialist

Find difference in months

Sometimes we need to find the difference between two dates in complete months.

In the below example, we have the start date and the end date of a project.

Calculate the difference between two dates in complete months

Now, you need to find the number of complete months between the given start date and the end date.

You can follow the below steps for that.

  1. Enter an equal sign and type the DATEDIF.

The syntax of the DATEDIF function is DATEDIF(start_date,end_date,unit).

=DATEDIF

Enter an equal sign and type the DATEDIF
  1. Open a parenthesis and select the start date in column A as the first argument of the function.

Then, your DATEDIF formula is;

=DATEDIF(A2

Open a parenthesis and select the start date as the first argument
  1. Enter a comma and select the ending date in column B as the second argument of the DATEDIF formula.

The updated DATEDIF formula is;

=DATEDIF(A2,B2

 Select the ending date as the second argument of the DATEDIF formula
  1. Next, enter the time unit. As you want the difference in complete months, you have to enter the letter M within quotes.

The following formula should be in the formula bar

=DATEDIF(A2,B2,”M”

Enter the final argument - Unit - DATEDIF function
  1. Finally, close the parenthesis and press the Enter key. Then, the DATEDIF function returns the number of complete months between two date values.

Then, the formula is;

=DATEDIF(A2,B2,”M”)

Use the DATEDIF function to find date difference in months

As we have used “M” as the time unit, the DATEDIF rounds the difference to complete months.

So, in the above example, we will get 16 as the number of full months between the given two days.

Assume that you need to find the difference in months ignoring years between two dates.

In that situation, you have to use the “YM” as the time unit instead of “M” πŸ₯³

Kasper Langmann, Microsoft Office Specialist

Find difference in years

We occasionally need to find the exact number of full years between two dates.

Excel’s DATEDIF function can be used in such situations as well.

In the example below, we have a person’s birthday and the current date πŸŽ‚

Calculate the number of years between a period

Now, you have to find the age of that person in years.

The DATEDIF function can also be used to calculate the age. For that, you simply require a slightly different formula.

  1. Enter an equal sign and type the DATEDIF.

The syntax of the DATEDIF function is DATEDIF(start_date,end_date,unit).

=DATEDIF

Enter an equal sign and type the DATEDIF
  1. Open a parenthesis and select the birthday as the first argument of the function.

Then, your DATEDIF formula is;

=DATEDIF(A2

Open a parenthesis and select the birthday as the first argument
  1. Enter a comma and select the current date in column B as the second argument of the DATEDIF formula.

The updated DATEDIF formula is;

=DATEDIF(A2,B2

 Select the current date as the second argument of the DATEDIF formula
  1. Next, enter the time units. As you want the difference in complete years, you have to enter the letter Y within quotes.

The following formula should be in the formula bar.

=DATEDIF(A2,B2,”Y”

Enter the final argument - Time units - DATEDIF function
  1. Finally, close the parenthesis and press the Enter key. Then, the DATEDIF function returns the number of complete years.

Then, the formula is;

 =DATEDIF(A2,B2,”Y”)

Use the DATEDIF function to find age

So, you get the age of that person as 34 years.

If you have to find the ages of many people you can drag the same formula for all the dates πŸ€—

That’s it – Now what?

Good job! You can now quickly calculate the difference between dates in Excel πŸ‘

Whether you want the difference in days, months, or years, the DATEDIF function in Excel does the work.

That’s Microsoft Excel–willing to do the work for you. You just need a strong foundation on how to correctly build Excel formulas and functions.

You may find a lot of available articles online about all Excel formulas and functions you should learn. That could be overwhelming 😩

Let’s make learning Excel easy, quick, and practical for youπŸ‘

My FREE Advanced Excel Training adapts to your Excel skill level so you won’t get overwhelmed but love learning ExcelπŸ˜€

Other resources

If the start_date is greater than the end_date in your DATEDIF function, then #NUM error appears.

To avoid this error, make sure that the end_date is always greater than the start_date.

Learn more about all Excel errors and how to fix them.

Are you often working with date and time formats in Excel? Our ultimate guide about Date and Time Functions will surely help you 😊

Frequently asked questions

The DATEDIF function is in all Excel versions. However, it is not listed as an Excel function. So, Excel will not show you the arguments of the function.

You can simply subtract 2 dates to find the difference in days.

There is a function called DAYS in Excel. It will also help you to calculate the difference between two dates in days.

If you need to find the difference between two dates assuming 360 days per annum, you can use the DAYS360 function.