Google Sheets DATEDIF Formula: Syntax and Examples

In Google Sheets, use DATEDIF(start_date, end_date, unit) to calculate the time between two dates in days, complete months, or complete years. With a start date in A2 and an end date in B2, enter =DATEDIF(A2,B2,"Y") to return the number of complete years between them.

DATEDIF syntax and what it returns

The function takes three arguments, in this order: the earlier date, the later date, and a text code specifying the unit to calculate. The start and end values should be valid dates, and the start date must not be later than the end date. DATEDIF returns a number; it does not return a label such as “years” or “months” unless you add one separately.

start_date: The beginning of the interval, such as a hire date or birth date.
end_date: The date to compare against. This can be a date in another cell or a function such as TODAY().
unit: A quoted code that determines whether Sheets counts days, complete months or complete years.

For example, with a start date in A2 and an end date in B2, =DATEDIF(A2,B2,"D") returns the number of elapsed days. The unit must be enclosed in straight double quotation marks. DATEDIF is a real Google Sheets function, though it may not appear in the formula suggestions as you type. You can enter it directly.

What the DATEDIF units mean

The unit determines whether the result counts complete calendar intervals or a particular kind of day or month difference. The distinction matters: a result in complete months is not necessarily the same as the number of calendar-month boundaries crossed.

“Y”: Complete years. A date from 15 June 2021 to 14 June 2022 is not yet one complete year; 15 June 2022 is.
“M”: Complete months. This counts whole months that have elapsed, not the difference between the month numbers on the calendar.
“D”: Elapsed days between the dates. The start date itself is not counted as an extra day.
“YM”: Remaining months after complete years are removed. This is useful for expressing a duration as years plus leftover months.
“YD”: Days remaining after complete years are removed. It ignores the year difference when calculating the remainder.
“MD”: Days remaining after months and years are ignored. Use this cautiously: this unit can produce surprising results for some date combinations.

Use uppercase unit codes in quotes to make formulas easy to read. The important practical distinction is that Y and M count complete units. For instance, a span of 11 months and 29 days returns zero for “Y”, while a span of one month and 29 days returns one for “M”.

Worked examples with dates

Suppose A2 contains 15 January 2022 and B2 contains 20 June 2024. These examples use the same two date cells, so you can change the unit to see different ways of measuring the interval.

With those dates in A2 and B2, enter =DATEDIF(A2,B2,"Y"). The result is 2, because two complete anniversaries have passed.
With those dates in A2 and B2, enter =DATEDIF(A2,B2,"M"). The result is 29 complete months: the 29th monthly anniversary is 15 June 2024.
With those dates in A2 and B2, enter =DATEDIF(A2,B2,"D"). The result is 887 elapsed days.
With those dates in A2 and B2, enter =DATEDIF(A2,B2,"YM"). The result is 5 leftover months after the two complete years are removed.

DATEDIF measures elapsed time, rather than counting both endpoints as separate days. If a task runs from Monday through Wednesday and you want to count all three calendar dates inclusively, elapsed days alone gives two. In that situation, decide whether the task requires an inclusive count before choosing a formula; adding one is appropriate only when both the start and end dates should count as days worked or covered.

Calculate age or time in service

A common reason to use DATEDIF is to calculate someone’s age in completed years. With a birth date in A2, enter =DATEDIF(A2,TODAY(),"Y"). The formula returns the person’s age on the current date, updating when the sheet recalculates. It does not round up: someone turns a new age on their birthday, not at the beginning of that calendar year.

The same pattern works for elapsed service, membership or tenure. With a start date in A2 and a fixed end date in B2, use =DATEDIF(A2,B2,"Y") for complete years. If the period is ongoing, use =DATEDIF(A2,TODAY(),"Y") instead. For a breakdown into complete years and remaining months, keep the same start and end dates in A2 and B2 and use =DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months". For example, the January 2022 to June 2024 dates above produce “2 years, 5 months.”

These formulas are useful for ordinary tracking, but a single age or tenure number may not meet requirements for official records, eligibility rules or policies. Those rules can define dates and cutoffs in specific ways. Confirm the applicable rule when the result will determine a formal decision.

DATEDIF compared with subtracting dates

Google Sheets stores valid dates as numeric values, so subtracting one date from another can give the elapsed day count. With the start date in A2 and the end date in B2, =B2-A2 returns 887 for the worked example. This is equivalent to DATEDIF with the “D” unit for ordinary date values, and subtraction is often the more straightforward choice when days are all you need.

DATEDIF is more convenient when the question is about completed years or months. Subtracting year numbers, such as 2024 minus 2022, would return 2 even if the second anniversary has not happened yet. DATEDIF with “Y” accounts for whether that anniversary has passed. Similarly, subtracting month numbers does not by itself tell you how many complete months elapsed. Choose the formula that matches the question: elapsed days, complete months, complete years, or a remainder.

Check that your cells contain real dates

A date can look correct on screen but still be stored as text. DATEDIF needs date values it can interpret, not arbitrary text strings. If you see an error despite apparently valid dates, check how the values were entered or imported. Select a date cell and look at the formula bar; a value that was pasted from a report or imported as text may not behave like a date.

Use the Google Sheets menu Format > Number > Date to apply a date display format to selected cells. Changing the display format does not always convert text into a real date, however. If dates were imported in a format that conflicts with the spreadsheet’s locale, confirm the locale under File > Settings and convert the values if needed. A date such as 03/04/2024 can mean March 4 or April 3 depending on the date convention, so an unambiguous format or correctly parsed date is important.

For a small sheet, re-entering the value as a recognized date may be simplest. For a larger imported dataset, check several rows before filling a formula down; otherwise, a mixture of text and date values can create inconsistent results. Formatting can change how a valid date appears, but it cannot resolve uncertainty about which day and month an ambiguous text value was intended to represent.

Common errors and how to fix them

An error appears when the start date is later: DATEDIF expects the start date to be earlier than or equal to the end date. Check the cell references and the chronological order.
The result is unexpectedly small: Confirm the unit. “Y” counts complete years, not the difference between year numbers; “M” counts complete months, not all month boundaries.
The formula is rejected: Check for missing parentheses, commas, or quotation marks. The unit must be quoted, as in =DATEDIF(A2,B2,"M").
A date looks right but does not calculate: The cell may contain text rather than a date value. Verify the input and the spreadsheet locale instead of relying only on its appearance.
The displayed answer looks like a date: The result cell may have a date number format. Change it using Format > Number > Number or choose an appropriate number format.

It is also easy to confuse elapsed days with inclusive days. From 1 January to 1 January of the next year, DATEDIF with “D” returns the number of days between those dates, which depends on whether the interval includes a leap day. It does not count the first date as an additional day. Be explicit about whether your work, billing or attendance rule counts the first and last date.

Choosing the right approach for your sheet

Before building a column of date calculations, decide what the output should represent and how it will be used. A number of days is useful for turnaround time; complete months may suit a subscription duration; complete years are common for age or tenure. A duration such as “2 years, 5 months” is easier to read, but separate numeric columns are often easier to sort, filter and analyze later.

Use “D” or direct subtraction when you need elapsed days.
Use “M” or “Y” when you need complete months or years.
Use “YM” for leftover months after complete years, rather than subtracting calendar month numbers yourself.
Check how blanks, future dates and reversed dates should be handled in your particular dataset before copying a formula down.

If you add words to a result with a formula, the output becomes text, which is convenient for a report but less convenient for later numeric calculations. Keeping the numeric result in one cell and a written label in another can make a sheet more flexible. In Google Sheets, the core DATEDIF function works in the browser; Excel also supports DATEDIF for compatibility, although it may not display the function in its autocomplete list either.

Frequently Asked Questions

Does Google Sheets have a DATEDIF function?
Yes. Enter it directly as DATEDIF(start_date,end_date,unit). It may not appear in the autocomplete suggestions, but that does not mean the function is unavailable.

How do I calculate the number of months between two dates?
With the earlier date in A2 and the later date in B2, use =DATEDIF(A2,B2,"M") to return the number of complete months. A partial final month is not counted as a whole month.

How do I calculate age in Google Sheets?
With the birth date in A2, use =DATEDIF(A2,TODAY(),"Y") for completed years as of today. Make sure A2 contains a valid date and is not a future date.

Why does DATEDIF return an error?
Check that both inputs are valid dates, the start date is not after the end date, and the unit is in quotation marks. Imported date text and reversed cell references are common causes.

Does DATEDIF count the start date?
No. Its “D” result is the elapsed difference between the dates, not an inclusive count of every calendar date touched. Add an inclusive-day adjustment only when your counting rule requires it.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.