Practice free

DATEDIF function

DATEDIF counts the complete years, months or days between two dates and returns a whole number, which makes it a common choice for ages and tenure. It still works, but it is undocumented in the function wizard.

Example

Read it as a sentence: count the complete years between January 15, 2020 and March 10, 2024, which gives 4.

Practice this in the app

Syntax

DATEDIF(start_date, end_date, unit)

start_date (required)
The earlier date.
end_date (required)
The later date.
unit (required)
Text code: "Y" whole years, "M" whole months, "D" days, "MD" days ignoring months and years, "YM" months ignoring years, "YD" days ignoring years.

Returns: A whole number of years, months or days between the two dates.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=DATEDIF(DATE(2024,1,15),DATE(2026,3,10),"y") 2 Years
=DATEDIF(DATE(2024,1,15),DATE(2026,3,10),"m") 25 Months
=DATEDIF(DATE(2024,1,15),DATE(2026,3,10),"d") 785 Days
=DATEDIF(DATE(2024,1,15),DATE(2026,3,10),"md") 23 Md unit
=DATEDIF(DATE(2024,1,15),DATE(2026,3,10),"ym") 1 Ym unit
=DATEDIF(DATE(2024,1,15),DATE(2026,3,10),"yd") 54 Yd unit
=DATEDIF(DATE(2026,3,10),DATE(2024,1,15),"y") #NUM! End before start
=DATEDIF(DATE(2024,1,15),DATE(2026,3,10),"x") #NUM! Bad unit

Watch out for

  • Not listed in autocomplete, so you must type it fully.
  • If start_date is later than end_date you get #NUM!.
  • The "MD" unit can give wrong or negative-looking results; avoid it.

Where it works

Excel
DATEDIF is in every version of Excel from 2007 on. Hidden function kept for Lotus 1-2-3 compatibility; works in all versions but has no argument tooltip.
Google Sheets
Same name, same behaviour for everyday use. Sheets documents DATEDIF; behaviour matches for Y, M and D.

Part of Date and time functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .