DATEDIF function in Google Sheets

DATEDIF returns the number of complete years, months or days between two dates.

Ages and lengths of service are counted in completed years and months. Subtracting two dates gives days, and dividing days by 365 is slightly wrong. DATEDIF counts the way people do: a year is added on the anniversary and not before.

The examples use a fixed date so that their results stay the same. In your own sheet, put TODAY() there.

Syntax

=DATEDIF(start_date, end_date, unit)

Argument	What it is
`start_date`	The earlier date.
`end_date`	The later date.
`unit`	"Y" complete years, "M" complete months, "D" days, "YM" months beyond the years, "MD" days beyond the months, "YD" days beyond the years.

Examples

Sample sheet: Staff with their date of birth and date of joining, and a short list of holidays.

	A	B	C	D
1	Name	Born	Joined	Holiday
2	Anita	19-Jul-1988	01-Apr-2019	26-Jan-2026
3	Rohit	30-Nov-1992	15-Jun-2020	04-Mar-2026
4	Deepa	29-Feb-1996	01-Sep-2021	15-Aug-2026
5	Manoj	05-Dec-1999	10-Jan-2022	02-Oct-2026

=DATEDIF(B2,DATE(2026,11,12),"Y")
Result: 38
Age in completed years on 12 November 2026, for someone born on 19 July 1988.
=DATEDIF(B3,DATE(2026,11,12),"Y")
Result: 33
Born 30 November 1992: the birthday has not yet come this year.
=DATEDIF(C2,DATE(2026,11,12),"M")
Result: 91
Complete months of service since 1 April 2019.
=DATEDIF(C2,DATE(2026,11,12),"D")
Result: 2782
Days, the same as subtracting the dates.
=DATEDIF(C2,DATE(2026,11,12),"YM")
Result: 7
Months left over after the complete years.
=DATEDIF(C2,DATE(2026,11,12),"Y")&" years, "&DATEDIF(C2,DATE(2026,11,12),"YM")&" months"
Result: 7 years, 7 months
Length of service in words.
=DATEDIF(DATE(2026,11,12),C2,"Y")
Result: #NUM!
The later date given first is an error.

The units

Unit	Counts
`"Y"`	complete years
`"M"`	complete months
`"D"`	days
`"YM"`	months after the last complete year
`"MD"`	days after the last complete month
`"YD"`	days after the last anniversary

The unit is text and needs quotation marks.

Age

=DATEDIF(B2, TODAY(), "Y") is the age today. It rises by one on each birthday without anyone touching the sheet.

For an age on a cut-off date, such as the eligibility date of an examination, put that date in place of TODAY.

In years, months and days:

=DATEDIF(B2,TODAY(),"Y")&" y "&DATEDIF(B2,TODAY(),"YM")&" m "&DATEDIF(B2,TODAY(),"MD")&" d"

A whole column of ages

=ARRAYFORMULA(IF(B2:B="", "", DATEDIF(B2:B, TODAY(), "Y"))) fills the column and extends to new rows. The guard matters here: an empty date counts as day zero, long ago, and would show an age of over a hundred.

The earlier date first

If the start is later than the end, the result is #NUM!. When either order can occur, sort them inside the formula: =DATEDIF(MIN(A2,B2), MAX(A2,B2), "D").

Real dates only

DATEDIF needs true date values. A date typed in a form the spreadsheet's locale does not recognise stays as text, at the left of the cell, and gives #VALUE!. The locale, under File, Settings, decides whether 03/04/2026 is the 3rd of April or the 4th of March. If imported dates are being misread, check it.

=ISDATE(A2) tests a cell.

Other ways to measure a gap

Days: =B2-A2, or =DAYS(B2, A2).
A fraction of a year: =YEARFRAC(A2, B2).
Working days: =NETWORKDAYS(A2, B2, holidays).
Weeks: =INT((B2-A2)/7).

Common mistakes

Dates in the wrong order.
The unit without quotation marks.
Empty cells treated as very old dates.
Day and month swapped because of the locale.
Using TODAY for something that should be fixed, such as age at admission.

In Excel

DATEDIF works in Excel with the same units, but Excel does not list or document it in its function help, so you must type it from memory. In Google Sheets it is an ordinary, documented function.

Related functions

The results on this page were worked out by us from the sample sheet; they were not produced by a script running Google Sheets. If Sheets shows you something different, please tell us.

This is an independent help site. It is not connected with or approved by Google. Google Sheets is a trademark of Google LLC, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.