DATEDIF calculates the difference between two dates in whole years, whole months or days. Its syntax is =DATEDIF(start_date, end_date, unit), where unit is one of six text codes: "Y", "M", "D", "MD", "YM" or "YD". This guide explains each one with the same example, so you can see how they fit together.
Tested in Google Sheets. On November 9, 2026 we entered the formulas on this page in Google Sheets, all six units, Google’s own month-end example and the error cases, and compared the results with values calculated independently. How we test
The six units at a glance
In every example below, the start date in A2 is August 20, 2023 and the end date in B2 is March 7, 2026.
| Unit | What it returns | Formula | Result |
|---|---|---|---|
"Y" |
Whole years | =DATEDIF(A2, B2, "Y") |
2 |
"M" |
Whole months | =DATEDIF(A2, B2, "M") |
30 |
"D" |
Days | =DATEDIF(A2, B2, "D") |
930 |
"YM" |
Months left after removing whole years | =DATEDIF(A2, B2, "YM") |
6 |
"MD" |
Days left after removing whole months | =DATEDIF(A2, B2, "MD") |
15 |
"YD" |
Days left after removing whole years | =DATEDIF(A2, B2, "YD") |
199 |
“Y”: whole years
Counts complete years only. From August 20, 2023 to March 7, 2026 there are 2 full years: the third year would only be complete on August 20, 2026.
“M”: whole months
Counts complete months. A month only counts once the end date reaches the same day number as the start date. Here, 30 months take you to February 20, 2026; the 31st month would be complete on March 20, so the result is 30.
This rule has a surprising effect at the end of the month. Google’s own example: from September 30, 2015 to February 28, 2016, DATEDIF returns 4 months, not 5, because February 28 never reaches day 30.
“D”: days
The total number of days, 930. It gives the same result as subtracting the dates (=B2-A2) or =DAYS(B2, A2). See all the ways to count days between dates.
“YM”: months after the whole years
Removes the complete years and counts the complete months that remain. After 2 years (to August 20, 2025), there are 6 more complete months (to February 20, 2026).
“MD”: days after the whole months
Removes the complete months and counts the days that remain. From February 20, 2026 (the end of the 30th month) to March 7, 2026 there are 15 days.
Found in testing: “MD” can return a negative number. From January 31 to March 1, 2026, DATEDIF(…, "MD") returned -2 in Google Sheets, even though the dates are in the right order. The month-end section below explains how to avoid it.
“YD”: days after the whole years
Removes the complete years and counts the remaining days. From August 20, 2025 to March 7, 2026 there are 199 days. A common use is counting the days since the last birthday or anniversary.
Practical example: exact age in years, months and days
Combine "Y", "YM" and "MD" to show an age like “36 years, 5 months, 28 days”. With the date of birth in A2:
=DATEDIF(A2, TODAY(), "Y") & " years, " & DATEDIF(A2, TODAY(), "YM") & " months, " & DATEDIF(A2, TODAY(), "MD") & " days"
For a date of birth of May 12, 1990, on November 9, 2026 this returns 36 years, 5 months, 28 days. Because it uses TODAY(), the result updates every day.
If you only need the age in years, =DATEDIF(A2, TODAY(), "Y") is enough. Our guide to calculating age from a date of birth covers ages on a specific date, whole columns and February 29 birthdays.
Common errors
#NUM! error
There are two common causes:
- The start date is later than the end date. DATEDIF does not return negative results: check the order of the first two arguments, which is the reverse of
DAYS. - The unit is not one of the six codes. A typo such as
"YX"also returns #NUM!.
#NAME? error
The unit is missing its quotes. Write "YM", not YM: without quotes, Google Sheets looks for a named range called YM.
#VALUE! error
One of the dates is text that Google Sheets cannot read as a date. This often happens with dates typed as text in a different regional format, such as "7/16/1969" in a spreadsheet set to a locale that writes the day first.
The result looks like a date
If a result such as 8 appears as a date in January 1900, the cell has a date format. Change it with Format → Number → Number.
Unexpected results with “MD” at the end of the month
When the start date is on the 29th, 30th or 31st, some months do not have that day, and “MD” can give results that are hard to explain. For example, =DATEDIF(DATE(2026,1,31), DATE(2026,3,1), "MD") returns -2, a negative number of days, even though the start date is earlier than the end date. If you need exact day counts, use "D" or "YD", which are not affected.
DATEDIF vs DAYS vs subtraction
| Subtraction | DAYS |
DATEDIF |
|
|---|---|---|---|
| Argument order | end − start | end, start | start, end |
| Returns months or years | No | No | Yes |
| Start date after end date | Negative number | Negative number | #NUM! error |