How do I calculate the number of days, months, or years between two dates?
DATEDIF calculates the difference between two dates in whichever unit you choose: =DATEDIF(start_date, end_date, unit), where unit is "D" for days, "M" for complete months, or "Y" for complete years.
Formula (Excel and Google Sheets (Google Sheets documents DATEDIF directly; Excel supports it and calculates correctly, but does not list it in the function autocomplete menu))
=DATEDIF(A2,B2,"Y")DATEDIF takes a start date, an end date, and a unit code telling it how to measure the gap between them. "D" gives the total number of days, "M" gives the number of complete months that have passed, and "Y" gives the number of complete years, which is the most common use, calculating someone's age or how long an account has been open.
Make sure start_date comes before end_date; DATEDIF returns a #NUM! error if the start date is later than the end date. In Excel, you can still type DATEDIF directly into a cell and it will calculate correctly, even though it will not appear in the function suggestion list as you type.
Step by step
- 1
Identify the start and end dates
Confirm the start date is chronologically earlier than the end date.
- 2
Choose your unit
"Y" for complete years, "M" for complete months, or "D" for total days.
- 3
Write =DATEDIF(start_date, end_date, unit)
e.g. =DATEDIF(A2,B2,"Y") for complete years between the two dates.
Common questions
Why does DATEDIF return a #NUM! error?
This happens when the start date is later than the end date. Swap the two arguments or check for a data entry mistake.
Can I get years, months, and days together, like "2 years, 3 months"?
Yes, by combining DATEDIF with different unit codes ("Y", then "YM" for the remaining months) and joining the results into one piece of text with TEXTJOIN or &, though this gets complex quickly for a simple age calculation.
Related formulas
How to Use TODAY and NOW for a Live Date, and to Calculate Age or Days Elapsed
RelatedHow to Format Text and Dates with the TEXT Formula
RelatedHow to Split One Column of Text into Several Columns
From the blogHow to Make Sense of Your Spreadsheet Data Without Being an Analyst
Want a formula written for your exact spreadsheet, not a generic example?
Try the free formula generator