How to Use DATEDIF in Excel: Calculate Date Differences Easily
Use the
DATEDIF function in Excel to find the difference between two dates. It calculates the difference in days, months, or years depending on the unit you specify, like "d" for days or "m" for months.Syntax
The DATEDIF function has three parts:
- start_date: The first date.
- end_date: The second date (must be later than start_date).
- unit: The type of difference you want, like days, months, or years.
Example units: "d" for days, "m" for months, "y" for years.
excel
DATEDIF(start_date, end_date, unit)
Example
This example shows how to calculate the number of days between January 1, 2023, and March 1, 2023.
excel
=DATEDIF(DATE(2023,1,1), DATE(2023,3,1), "d")
Output
59
Common Pitfalls
Common mistakes include:
- Using
end_dateearlier thanstart_date, which causes an error. - Using incorrect unit codes like
"D"instead of lowercase"d". - Forgetting that
DATEDIFis not listed in Excel’s formula suggestions but still works.
excel
=DATEDIF(DATE(2023,3,1), DATE(2023,1,1), "d") <em>(wrong: end_date before start_date)</em> =DATEDIF(DATE(2023,1,1), DATE(2023,3,1), "d") <em>(correct)</em>
Quick Reference
| Unit | Meaning | Example Output |
|---|---|---|
| "d" | Total days between dates | 59 |
| "m" | Total complete months between dates | 1 |
| "y" | Total complete years between dates | 0 |
| "ym" | Months ignoring years | 2 |
| "yd" | Days ignoring years | 59 |
| "md" | Days ignoring months and years | 0 |
Key Takeaways
Use DATEDIF to calculate differences between two dates in days, months, or years.
Always ensure the end_date is later than the start_date to avoid errors.
Use lowercase unit codes like "d", "m", and "y" for correct results.
DATEDIF is not shown in Excel’s formula list but works correctly when typed.
Use special units like "ym", "yd", and "md" to get partial differences.