Excel DATEDIF Function: Find Difference Between Dates (2024)
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.
Table of Contents
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.
Now, you have to find the number of days between the two dates.
You can do it by following the steps below.
- 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
- Open a parenthesis and select the initial date (start date) as the first argument of the function.
Then, your DATEDIF formula is;
=DATEDIF(A2
- Enter a comma and select the end date as the second date of the DATEDIF formula.
The updated DATEDIF formula is;
=DATEDIF(A2,B2
- 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”
- 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”)
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 SpecialistFind 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.
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.
- Enter an equal sign and type the DATEDIF.
The syntax of the DATEDIF function is DATEDIF(start_date,end_date,unit).
=DATEDIF
- 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
- 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
- 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”
- 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”)
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 SpecialistFind 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 🎂
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.
- Enter an equal sign and type the DATEDIF.
The syntax of the DATEDIF function is DATEDIF(start_date,end_date,unit).
=DATEDIF
- Open a parenthesis and select the birthday as the first argument of the function.
Then, your DATEDIF formula is;
=DATEDIF(A2
- 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
- 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”
- 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”)
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 😊