DATEDIF

Excel Functions

Get days, months, or years between two dates

Syntax

EXCEL

=DATEDIF(start_date, end_date, unit)

Arguments

Parameter	Description
`start_date`	Start date in Excel date serial number format.
`end_date`	End date in Excel date serial number format.
`unit`	The time unit to use (years, months, or days).

Return Value

The time between two dates in a given unit

Details

The DATEDIF function calculates the difference between two dates in years, months, or days. The result is always a whole number, because DATEDIF rounds down to the last complete interval. The desired interval is controlled by the unit argument, which supports six text codes as shown in the table below: The first three units ("y", "m", "d") return total intervals. The last three ("ym", "yd", "md") return remaining intervals, which is useful for breaking elapsed time into components. For example, to express a duration as "X years, Y months, Z days", you would use "y" for years, "ym" for remaining months, and "md" for remaining days. The status of DATEDIF in Excel is somewhat mysterious. DATEDIF (Date + Dif) is a "compatibility" function that comes from Lotus 1-2-3 way back in the 1990s. Alth

Examples

Basic usage

EXCEL

=DATEDIF("1-Jan-2023","1-Mar-2025","y") // returns 2 (complete years)
=DATEDIF("1-Jan-2023","1-Mar-2025","m") // returns 26 (complete months)
=DATEDIF("1-Jan-2023","1-Mar-2025","d") // returns 790 (total days)
=DATEDIF("1-Jan-2023","1-Mar-2025","ym") // returns 2 (months after years)
=DATEDIF("1-Jan-2023","1-Mar-2025","yd") // returns 59 (days after years)
=DATEDIF("1-Jan-2023","1-Mar-2025","md") // returns 0 (days after months)

Difference in days

DATEDIF can calculate the difference between dates in days in three ways, using the "d", "yd", and "md" units. In the worksheet below, the goal is to

EXCEL

=DATEDIF(B5,C5,"d") // total days
=DATEDIF(B5,C5,"yd") // days ignoring years
=DATEDIF(B5,C5,"md") // days ignoring months and years

Difference in months

DATEDIF can calculate the difference between dates in months in two ways, using the "m" and "ym" units. In the worksheet below, the goal is to calcula

EXCEL

=DATEDIF(B5,C5,"m") // complete months
=DATEDIF(B5,C5,"ym") // months ignoring years

Difference in years

DATEDIF calculates the difference between dates in complete years with the "y" unit. In the worksheet below, the goal is to calculate the number of co

EXCEL

=DATEDIF(B5,C5,"y") // complete years

Years, months, and days between dates

DATEDIF's ability to return different time components makes it ideal for expressing elapsed time as a combination of years, months, and days. In the w

EXCEL

=DATEDIF(B5,C5,"y") // years
=DATEDIF(B5,C5,"ym") // months
=DATEDIF(B5,C5,"md") // days

Years, months, and days between dates

Together, the three results add up to the total time elapsed between the start date and end date. To display time elapsed as a single result like "X y

EXCEL

=DATEDIF(B5,C5,"y")&" years, "&DATEDIF(B5,C5,"ym")&" months, "&DATEDIF(B5,C5,"md")&" days"

DATEDIF

Syntax

Arguments

Return Value

Details

Examples

See Also