DATEDIF is one of the most used Excel functions. In this article, you’ll learn how you can use this DATEDIF function efficiently with different criteria and by combining it with other functions in Excel.
Introduction to DATEDIF Function
Summary:
- The DATEDIF function returns the difference of days, months, or years between two dates, based on three arguments, one starting end, one ending date, and one argument called unit.
- The unit argument tells Excel whether the difference of days, months, or years is to be provided.
- The DATEDIF function includes only the ending date while counting, not the starting date.
- However, DATEDIF is a hidden function of Excel. That means you can use it, but Excel will not help you by providing any syntax or short notes while you are typing it.
Syntax:
=DATEDIF(start_date,end_date,unit)Arguments:
| Argument | Required/Optional | Value |
| start_date | Required | The date from where you want to count the difference. It can be a single date or an array of dates. |
| end_date | Required | The date where you want to stop counting. Additionally, it also can be a single date or an array of dates. |
| unit | Required | A text value that tells Excel whether the difference of days, months or years is to be returned. |
- DATEDIF counts a total of six different types of differences. It depends on the argument unit.
| Unit | Difference Type |
| “d” | Days between two dates. |
| “m” | Months between two dates. |
| “y” | Years between two dates. |
| “md” | Days between two dates ignoring months and years. |
| “ym” | Months between two dates ignoring years. |
| “yd” | Days between two dates ignoring years. |
- It includes only the ending date, not the starting date while counting.
- The DATEDIF function has the Array Formula for both the start_date and the end_date arguments. That means, it can accept an array of dates in place of a single date in the start_date argument and can return the output for each of the start_dates together. Same for the end_date argument.
Return Value:
It returns a total of six different types of outputs.
- Days between two dates.
- Months between two dates.
- Years between two dates.
- Days between two dates ignoring the months and years.
- Months between two dates ignoring the years.
- Days between two dates ignoring the years.
How to Use DATEDIF Function in Excel: 2 Ideal Examples
We can use the DATEDIF function in Excel to count days, months, or years between two days. Additionally, it can be used to calculate age from a given birthdate combined with the TODAY function.
1. Calculate Difference Between Two Dates Using DATEDIF Function
Here, we have the Start Date and End Date of six employees of a company. Now, we will show you how you can calculate the difference between these two dates using the DATEDIF function.
In the function, we will insert the value of the unit on the basis of which parameter we want to get.
1.1 Determine Number of Days
Firstly, we will show you how you can determine the number of days between two dates using the DATEDIF function.
Steps:
- In the beginning, select Cell D5 and insert the following formula.
=DATEDIF(B5,C5,"d")- Then, press Enter.
- After that, drag down the Fill Handle tool to autofill this formula for the rest of the cells.
- Thus, you can get the days between two dates using this function.
1.2 Count Number of Months
Secondly, here you will find a way to count the number of months between two dates.
Steps:
- Firstly, select Cell D5 and insert the following formula.
=DATEDIF(B5,C5,"m")- Then, press Enter.
- After that, drag down the Fill Handle tool to autofill this formula for the rest of the cells.
- Thus, you can get the month differences between the two dates.
Note: When the day of the end_date is less than the day of the start_date, it doesn’t count the last month.
1.3 Determine Number of Years
Similarly, we can determine the number of years between two dates by just changing the unit value.
Steps:
- To start with, insert the following formula in Cell D5.
=DATEDIF(B5,C5,"y")- Then, press Enter and lower the cursor to fill the rest of the data cells.
- As a result, we will have a difference in years unit.
Note: When the month of the end_date is less than the month of the start_date, it doesn’t count the last year.
1.4 Calculate Number of Days Ignoring Years
In this demonstration, we will use the DATEDIF function to calculate the difference between two dates in days, ignoring years.
Steps:
- Firstly, choose Cell D5 and type the following formula.
=DATEDIF(B5,C5,"yd")- After that, press Enter and lower the cursor to the last data cell to get results according to the formula.
- Consequently, we will find the differences in days after ignoring the years while calculating all the values.
Note: When the day of the end_date is less than the day of the start_date, it counts days from one month before the end_day month.
1.5 Find Out Days Excluding Years & Months
After that, we will show you how you can find out the days excluding the years and months.
Steps:
- In the beginning, choose Cell D5 and write the following formula down.
=DATEDIF(B5,C5,"md")- Then, press the Enter button.
- Consequently, we will find a difference in days after ignoring both the years and months while calculating.
- Finally, move the cursor down to the last data cell to get results according to the formula.
- Here, as the unit argument is “md”, the function returns the difference of days ignoring the years and months between the starting date and the ending date.
1.6 Count Months Between Two Dates Ignoring Years
Lastly, in this final example, we will utilize the Excel DATEDIF function to calculate the difference between two dates in months, ignoring years.
Steps:
- Firstly, choose Cell D5 and type the following formula.
=DATEDIF(B5,C5,"ym")- After that, press Enter and lower the cursor to the last data cell to get results according to the formula.
- Consequently, we will find the differences in months after ignoring the years while calculating all the values.
2. Apply DATEDIF & TODAY Functions to Count Age in Excel
In the second example, we have a dataset containing the Name and Birthday of some students. Now, we will show you how you can count their age using the DATEDIF and TODAY functions in Excel.
However, the DATEDIF function can also take an array of dates as the start_date argument. Thus, instead of using one date at a time and then dragging the Fill Handle, we will use an Array Formula to enter all the starting dates together and can receive all the values of ages together in this example.
2.1 Determine Years
Firstly, we will determine the age in years. To do that, follow the steps given below.
Steps:
- In the beginning, insert the following Array Formula in Cell D6.
=DATEDIF(C6:C10,TODAY(),"y")
🔎 How Does the Formula Work?
- Firstly, in the DATEDIF function, we inserted cell range C6:C10 as start_date.
- Then, we used the TODAY function to get the date of today as end_date.
- Finally, we inserted “y” as the unit to get the value in years.
- Then, press Enter if you are using Excel 365, or else press Ctrl + Shift + Enter for other previous versions of Excel.
- In either case, you will get the age in years of each student up to today.
2.2 Calculate Months
After that, we will count the months ignoring the years as we have counted the age in years above.
Steps:
- Firstly, to count the months of the ages, enter this Array Formula in Cell E6.
=DATEDIF(C6:C10,TODAY(),"ym")
- Finally, press Enter or Ctrl +Shift + Enter depending on your Excel version.
2.3 Find Out Days
Finally, you can get the age in days ignoring the years and months using both DATEDIF and TODAY functions in Excel by going through the steps given below.
Steps:
- In the beginning, insert the following Array Formula in Cell D6.
=DATEDIF(C6:C10,TODAY(),"md")
- Thus, you can get the required ages in days.
Practice Section
In the article, you will find an Excel workbook like the image given below to practice on your own.
Common Errors with DATEDIF Function
| Error | When They Show |
| #Value! | When an argument is not of the specified type. For example, when the start_date or end_date argument is a text value, or the unit argument is anything other than the six specific text values. |
| #Num! | When the start_date is less than the end_date |
Download Practice Workbook
You are welcome to download the practice workbook from the link below.
Conclusion
Thus, you can use the Excel DATEDIF function in any formula to count the difference of days, months, or years between any two dates. Do you have any questions? Feel free to inform us.
<< Go Back to Excel Functions | Learn Excel
