DATEDIF

Calculates the number of days, months or years between two dates.

Syntax

DATEDIF(start_date, end_date, unit)

The function syntax has the following arguments:

  • start_date: Required. A date that represents the first, or starting date of a given period. Dates may be entered as text strings within quotation marks or cell references.
  • end_date: Required. A date that represents the last, or ending, date of the period.
  • unit: Optional, The type if duration to be calculated, if not used, then Days are the default.

“Y”

“M”

“D”

The number of complete years between the 2 dates

The number of complete months between the 2 dates

The number of complete days between the 2 dates

Specifics

  • The dates must have the format “D MMM YYYY”, eg. 3 Jun 2022
  • Month names must be 3 letters and in English only
  • If the start_date is greater than end_date, the result will be #Invalid
  • Do not use quotation marks in formula parameters for hard-coded dates, eg. =DATEDIF(“1 Jan 2000”, “2 Jan 2000”, “D”) will not work

Examples

Start DateEnd DateFormulaResult
01 Jan 2001   01 Jan 2003=DATEDIF(A2, B2, “Y”)2, complete years between dates
01 Jun 2001   15 Aug 2002=DATEDIF(A3, B3, “D”)440, days between dates
01 Jun 2001 15 Aug 2002 =DATEDIF(A4, B4, “M”)14, months between dates
01 Jan 2020 01 Jan 2010 =DATEDIF(A5, B5, “M”)#Invalid, end date before start date
01 Jan 2000 02 Jan 2000 =DATEDIF(A6, B6, “D”)1 day between dates
  =DATEDIF(1 Jan 2000, 2 Jan 2000, “D”)1 day between dates