How to Use DATEDIF in Google Sheets: Comprehensive Guide

DATEDIF calculates the difference between two dates in whole years, months or days. It is the standard way to get someone's age, an employee's tenure, or the months remaining on a contract, and it returns clean whole numbers rather than fractions.

Syntax

=DATEDIF(start_date, end_date, unit)

start_date: The earlier date.
end_date: The later date. It must not be before start_date, or you get #NUM!.
unit: A text code, in quotes, that says what to count.

Units

"Y": complete years between the dates
"M": complete months
"D": days
"YM": months after subtracting complete years (ignores years)
"YD": days after subtracting complete years (ignores years)
"MD": days after subtracting complete months (ignores months and years)

The unit is case-insensitive, but it must be quoted: "Y", not Y.

Basic examples

With a start date of January 15, 2020 in A2 and an end date of March 10, 2026 in B2:

=DATEDIF(A2, B2, "Y")

Returns 6: complete years.

=DATEDIF(A2, B2, "M")

Returns 73: complete months.

=DATEDIF(A2, B2, "D")

Returns the number of days. This equals B2 - A2.

Age and tenure

Age in years

=DATEDIF(A2, TODAY(), "Y")

With a birth date in A2. It counts only completed years, so someone turning 30 tomorrow is still 29 today.

Years, months and days in one phrase

=DATEDIF(A2, TODAY(), "Y") & " years, " & DATEDIF(A2, TODAY(), "YM") & " months, " & DATEDIF(A2, TODAY(), "MD") & " days"

Returns something like 6 years, 1 months, 23 days. The "YM" and "MD" units give the remainder after the larger units are removed, which is what makes the phrase work.

Employee tenure in whole months

=DATEDIF(A2, TODAY(), "M")

Months until a contract ends

=DATEDIF(TODAY(), B2, "M")

Make sure the end date is in the future, or the result is #NUM!.

Handling start dates after end dates

DATEDIF fails with #NUM! when the start is later than the end. Guard against it:

=IF(A2 <= B2, DATEDIF(A2, B2, "D"), "")

Or return the difference in either direction with MIN and MAX:

=DATEDIF(MIN(A2, B2), MAX(A2, B2), "D")

Using it across a column

=ARRAYFORMULA(IF(A2:A = "", "", DATEDIF(A2:A, TODAY(), "Y")))

Alternatives to DATEDIF

Days between: =B2 - A2 or =DAYS(B2, A2).
Fractional years: =YEARFRAC(A2, B2), which returns for example 6.15, useful for pro-rata calculations.
Working days: NETWORKDAYS.
Months, including partial: =(YEAR(B2) - YEAR(A2)) * 12 + MONTH(B2) - MONTH(A2), which counts calendar-month boundaries crossed.

Rounding behavior to know

DATEDIF counts complete units. From January 31 to February 28, "M" returns 0, because a full month has not elapsed. If you need to count month boundaries instead, use the YEAR/MONTH arithmetic above. Be careful with "MD" when the months have different lengths: results near month-ends can look surprising, so test with edge cases such as the 29th to the 31st before relying on it.

Common errors

#NUM!. The start date is after the end date.
#VALUE!. One of the dates is text rather than a real date, or the unit is invalid. Check alignment (dates are right-aligned by default) and convert with DATEVALUE.
#NAME?. The unit is not in quotes.
Unexpected 0. Fewer than one complete unit has passed.
Off-by-one age. DATEDIF counts completed years, which is the correct way to count age. For "age next birthday", add 1.

Frequently asked questions

Is the end date included? The difference is the elapsed time between the dates, so DATEDIF from Monday to Tuesday in days is 1. Add 1 if you want an inclusive count.

How do I get age as a decimal? YEARFRAC(A2, TODAY()).

Can DATEDIF take times? It ignores time-of-day and works on whole dates.

Related Functions

TODAY: Returns the current date.
NETWORKDAYS: Counts working days between two dates.
EOMONTH: Returns the last day of a month.
DAYS: Returns the number of days between two dates.
YEARFRAC: Returns the fraction of a year between two dates.

DATEDIF