One of the most common questions I’m asked whenever I do Microsoft Excel training involves performing calculations with dates.
We explored the NOW function and EDATE in other blog articles. This time we’ll explore DATEDIF.
What is DATEDIF? Here’s the deal.
People often want to calculate the amount of time that has elapsed between a two dates. That’s exactly what DATEDIF does!
The DATEDIF function in Excel is used to calculate the difference between two dates. DATEDIF calculates the duration between a start date and end date then returns the difference in days, months, or years.
DATEDIF is provided with Excel to maintain compatibility with Lotus 1-2-3 spreadsheets. Unlike most of Excel’s other functions, it won’t prompt you to enter the necessary arguments – but the syntax is fairly simple.
DATEDIF Function Syntax
The DATEDIF function accepts three arguments – all are required.
=DATEDIF(start_date,end_date,unit)
start_date can refer to a date displayed in a cell, it can be a string in quotes (“1/1/2022”), or it can be the output from other Excel functions.
end_date represents the last date in a period.
unit indicates whether you want DATEDIF to calculate the difference in days, months, or years. The unit argument isn’t case sensitive but should be enclosed in quotes.
Available units are described below.
| Unit | Returns |
|---|---|
| “Y” | Difference in complete years |
| “M” | Difference in complete months |
| “D” | Difference in days |
| “MD” | Difference in days, ignoring months and years |
| “YM” | Difference in months, ignoring years |
| “YD” | Difference in days, ignoring years |
Excel DATEDIF Function Examples
Let’s look at various ways you can use the Excel DATEDIF function to calculate the difference between two dates.
- In Example #1, we use the DATEDIF function to calculate the difference between two dates. Cell E2 the number of days as 911.
- Example #2 indicates that there were 29 complete months between the same two dates. Notice that our DATEDIF formula only returned the number of complete months.
- Example #3 shows two complete years.
- With MD specified as the unit in Example #4, the DATEDIF function returns a difference of 29 days, ignoring months and years.
- Example #5 specifies YM as the unit and the Excel DATEDIF function returns the difference as 5 months, ignoring days and years.
Final Thoughts on the DATEDIF Function in Excel
The Excel DATEDIF function is a simple and convenient way to calculate the number of days, months, or years, between two dates.
You can use the DATEDIF function when you want to calculate:
- Someone’s age
- The duration of a project
- Length of employment
Any time you have a start date and end date, the DATEDIF function in Excel returns the difference in days, months, or years.
What’s Next?
The DATEDIF function is one of many functions available in Excel that let you perform calculations using dates and times.
If you want learn about other Excel functions, you might want to check out XLOOKUP vs VLOOKUP: Excel Trainer Explains Key Differences. Both of these functions are super useful for combining data from multiple sources.
See you next time!
