Excel DATEDIF Function
last modified October 5, 2026
The DATEDIF function calculates the difference between two dates in
years, months, or days. It is a legacy compatibility function inherited from
Lotus 1-2-3, and although Excel still supports it, it does not appear in the
function wizard or in autocomplete. This tutorial provides a comprehensive guide
to using DATEDIF with detailed examples. You'll learn its syntax,
all six unit codes, practical applications such as age calculation, and the
known quirks that can trip you up.
DATEDIF Function Basics
The DATEDIF function measures the time between a start date and an
end date and returns the result in the unit you request. It always rounds down
to the last complete interval, so 11 months and 29 days returns 0 for the
"y" unit. Because DATEDIF comes from Lotus 1-2-3,
Excel keeps it for backwards compatibility but does not advertise it: typing
=DATEDIF( produces no autocomplete hint, and the function is absent
from the Insert Function dialog. Microsoft does document the function, but it
warns that one of its units, "md", can return incorrect results.
| Component | Description |
|---|---|
| Function Name | DATEDIF (Date Difference) |
| Syntax | =DATEDIF(start_date, end_date, unit) |
| Arguments | start_date, end_date, unit code |
| Return Value | Whole number of years, months, or days between the dates |
This table breaks down the essential components of the DATEDIF
function. All three arguments are required: the start date, the end date, and a
unit code supplied in double quotes.
If start_date is greater than end_date,
DATEDIF returns a #NUM! error. If either date cannot be
read as a valid date, it returns #VALUE!. Unit codes are not case
sensitive, so "ym" and "YM" are equivalent.
Basic DATEDIF Example (Days)
This example demonstrates calculating the number of days between two dates using
the "d" unit.
| A | B | C |
|---|---|---|
| Start Date | End Date | Result |
| 1/1/2023 | 1/31/2023 | =DATEDIF(A2,B2,"d") |
The table shows a simple spreadsheet with start and end dates in columns A and B. The formula in C2 calculates the days difference between these dates.
=DATEDIF(A2,B2,"d")
This formula returns 30, the number of days between January 1, 2023 and
January 31, 2023. The "d" unit counts the total days between the
dates. For a plain day count you can also subtract the dates,
=B2-A2, which gives the same result because Excel stores dates as
serial numbers.
DATEDIF for Months Difference
This example shows how to calculate complete months between two dates using the
"m" unit code.
| A | B | C |
|---|---|---|
| Start Date | End Date | Result |
| 3/15/2023 | 7/20/2023 | =DATEDIF(A2,B2,"m") |
The table demonstrates calculating complete months between March 15, 2023 and July 20, 2023. The function ignores the leftover days and counts only whole months.
=DATEDIF(A2,B2,"m")
This formula returns 4. March 15 to July 15 is four complete months, and the
extra five days are not enough to reach a fifth month. The "m"
unit code specifies complete months as the return unit.
DATEDIF for Years Difference
This example demonstrates calculating complete years between two dates using the
"y" unit code.
| A | B | C |
|---|---|---|
| Start Date | End Date | Result |
| 5/10/2018 | 5/9/2023 | =DATEDIF(A2,B2,"y") |
The table shows how to calculate years between May 10, 2018 and May 9, 2023. The formula counts complete years, so the day before the anniversary date returns one less year.
=DATEDIF(A2,B2,"y")
This formula returns 4 because May 9, 2023 is one day before the 5-year
anniversary on May 10, 2023. The "y" unit code counts complete
years and never rounds up. If you need a fractional number of years, use
YEARFRAC instead.
DATEDIF Unit Reference
DATEDIF supports six unit codes. The first three return total
intervals, and the last three return the remainder after larger components have
been counted. All six are shown below for the same date pair, January 1, 2023
to March 1, 2025.
| Unit Code | Description | Example | Result |
|---|---|---|---|
| "y" | Complete years between the dates | =DATEDIF("1/1/2023","3/1/2025","y") | 2 |
| "m" | Complete months between the dates | =DATEDIF("1/1/2023","3/1/2025","m") | 26 |
| "d" | Total days between the dates | =DATEDIF("1/1/2023","3/1/2025","d") | 790 |
| "ym" | Months remaining after complete years | =DATEDIF("1/1/2023","3/1/2025","ym") | 2 |
| "yd" | Days remaining after complete years | =DATEDIF("1/1/2023","3/1/2025","yd") | 59 |
| "md" | Days remaining after complete months, ignoring years | =DATEDIF("1/1/2023","3/1/2025","md") | 0 |
The "y", "m", and "d" units give the
total complete years, complete months, and days across the whole span. The
"ym", "yd", and "md" units break a
duration into components, for example "2 years, 2 months, 0 days". Note that
"md" is the one unit Microsoft explicitly warns about; the sections
below explain why.
DATEDIF for Months Ignoring Years ("ym")
This example shows how to calculate the remaining months after complete years
using the "ym" unit code.
| A | B | C |
|---|---|---|
| Start Date | End Date | Result |
| 8/25/2020 | 3/15/2023 | =DATEDIF(A2,B2,"ym") |
The table demonstrates calculating months remaining after complete years between
August 25, 2020 and March 15, 2023. The "ym" unit ignores the year
and day components.
=DATEDIF(A2,B2,"ym")
This formula returns 6. Two complete years run from August 25, 2020 to
August 25, 2022, and the remaining whole months from August 25 to March 15
number six, because March 15 falls short of the March 25 boundary. The
"ym" unit always returns a value from 0 to 11.
DATEDIF for Days Ignoring Years ("yd")
This example shows how to calculate days remaining after complete years using
the "yd" unit code. This unit is useful for comparing dates as if
they fell in the same year.
| A | B | C |
|---|---|---|
| Start Date | End Date | Result |
| 6/1/2001 | 8/15/2002 | =DATEDIF(A2,B2,"yd") |
The table demonstrates calculating days between June 1 and August 15 while ignoring the years 2001 and 2002. The result is the number of days from June 1 to August 15 within a single year.
=DATEDIF(A2,B2,"yd")
This formula returns 75, the number of days from June 1 to August 15 (30 days
in June, 31 in July, plus 14 days in August). The "yd" unit ignores
the year but keeps the month and day. It can be off by one day when the span
crosses a February 29 leap day, so test it for date pairs that cross a leap
year.
DATEDIF for Days Ignoring Months and Years ("md")
This example demonstrates calculating remaining days after complete months using
the "md" unit code. When the start day is not later than the end
day, this unit behaves as expected.
| A | B | C |
|---|---|---|
| Start Date | End Date | Result |
| 6/10/2022 | 8/25/2023 | =DATEDIF(A2,B2,"md") |
The table shows how to calculate days remaining after complete months between
June 10, 2022 and August 25, 2023. The "md" unit ignores the year
and month components.
=DATEDIF(A2,B2,"md")
This formula returns 15, because August 25 is 15 days after the 10th. Here the start day (10) is smaller than the end day (25), so the calculation is simple and safe. This is not always the case, as the next section explains.
The "md" Quirk
The "md" unit is the black sheep of DATEDIF. Microsoft
states that it "may result in a negative number, a zero, or an inaccurate
result" and recommends against using it. The problem appears when the start day
is greater than the end day, because DATEDIF subtracts the day
numbers and borrows days from a month without carrying correctly.
| Start Date | End Date | =DATEDIF(...,"md") |
|---|---|---|
| 1/29/2023 | 3/1/2023 | 0 |
| 1/30/2023 | 3/1/2023 | -1 |
| 1/31/2023 | 3/1/2023 | -2 |
All three date pairs span roughly one month and a few days, yet
"md" returns 0, -1, and -2. A negative number of days is
meaningless, which is why the unit is considered unreliable. When you need the
remaining days after complete months, use EDATE instead.
=C2-EDATE(A2,DATEDIF(A2,C2,"m"))
This formula counts the complete months with the "m" unit, adds
them back to the start date with EDATE, and subtracts the result
from the end date. EDATE handles shorter months correctly, so the
remaining days are always accurate. For January 31, 2023 to March 1, 2023 it
returns 1, the true number of days after the one complete month.
Calculating Age with DATEDIF
DATEDIF is commonly used to calculate age in years, months, and
days. The "y" unit alone gives age in complete years, which is what
most forms ask for. Combining three calls produces a full age string.
| A | B | C |
|---|---|---|
| Birth Date | Current Date | Age |
| 4/12/1985 | 4/4/2023 | =DATEDIF(A2,B2,"y") & " years, " & DATEDIF(A2,B2,"ym") & " months, " & DATEDIF(A2,B2,"md") & " days" |
The table demonstrates calculating a person's exact age from their birth date to
a current date. The formula combines three DATEDIF calls for a
complete result.
=DATEDIF(A2,B2,"y") & " years, " & DATEDIF(A2,B2,"ym") & " months, " & DATEDIF(A2,B2,"md") & " days"
This formula returns "37 years, 11 months, 23 days" for the given dates. It
combines the "y", "ym", and "md" units
with text concatenation to create a readable age string. To calculate age as of
today, replace B2 with TODAY().
Because the day portion relies on the fragile "md" unit, a more
robust version replaces it with the EDATE workaround. This formula
returns the same result for the dates above but stays correct for every date
pair.
=DATEDIF(A2,B2,"y") & " years, " & DATEDIF(A2,B2,"ym") & " months, " & B2-EDATE(A2,DATEDIF(A2,B2,"m")) & " days"
For simple age in whole years, =DATEDIF(A2,TODAY(),"y") is enough.
Because TODAY() is volatile, the result updates every time the
workbook recalculates.
DATEDIF for Project Duration
DATEDIF can express a project duration in months and days, which is
useful for project management tracking.
| A | B | C |
|---|---|---|
| Start Date | End Date | Duration |
| 10/15/2022 | 3/28/2023 | =DATEDIF(A2,B2,"m") & " months, " & DATEDIF(A2,B2,"md") & " days" |
The table shows how to calculate the duration of a project that started on October 15, 2022 and ended on March 28, 2023. The formula provides months and days.
=DATEDIF(A2,B2,"m") & " months, " & DATEDIF(A2,B2,"md") & " days"
This formula returns "5 months, 13 days" for the given date range. Five complete months run from October 15 to March 15, and 13 more days reach March 28. It is a convenient way to summarize project timelines in a readable format.
DATEDIF Errors and Troubleshooting
Most DATEDIF problems come from reversed dates, invalid date
values, or the unreliable "md" unit.
| Cause | Symptom | Fix |
|---|---|---|
| start_date is later than end_date | #NUM! error | Swap the arguments or sort the dates first |
| A date is text that Excel cannot parse | #VALUE! error | Enter real dates or convert text with DATEVALUE |
| Function name is misspelled | #NAME? error | Type DATEDIF exactly; it will not autocomplete |
| Unit code is not in double quotes | #NAME? error | Wrap the code in quotes, for example "m" |
| Using the "md" unit | Negative or inaccurate days | Replace it with the EDATE workaround |
| End-of-month dates with "m" or "ym" | Months undercounted by one | Expect whole-month anniversaries and test boundary dates |
The #NUM! error is by far the most common and is easy to spot:
DATEDIF refuses to count backwards. Wrap the call in
IFERROR if you want a friendlier message:
=IFERROR(DATEDIF(A2,B2,"d"),"Check the dates").
The DATEDIF function is a compact way to measure the distance
between two dates in years, months, or days, and it remains the quickest way to
build an age or tenure string. Its legacy status means you must type the full
name and remember the six unit codes yourself. Treat the "y",
"m", and "d" units as reliable, be careful with
"ym" and "yd" near month ends and leap days, and avoid
"md" altogether by using the EDATE workaround.
Mastering these details lets you calculate date differences accurately without
being surprised by the function's quirks.
Author
List all Excel Formulas.