What it does
DATEDIF takes a start date, an end date and a unit code. "y" counts completed years, "m" completed months and "d" days. "ym" gives the months left after removing whole years, "md" the days left after removing whole months, and "yd" the days left after removing whole years. The codes are always written in quotes.
The result is always the completed span: someone who joined on 15 March 2020 still has 5 years of service on 14 March 2026 and reaches 6 on 15 March. Using "y", "ym" and "md" side by side gives a span such as "6 years 7 months 21 days".
DATEDIF is kept for compatibility with older spreadsheet programs: it does not appear in the function list or in autocomplete, so you type it in yourself. The "md" unit can give unexpected results for some month-end dates. For years with decimals use YEARFRAC, for a difference in days DAYS, and for working days NETWORKDAYS.
Syntax
=DATEDIF(start_date, end_date, unit)=ETARİHLİ(başlangıç; bitiş; birim)Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
start_date | Required | The date the span starts. It cannot be later than the end date. |
end_date | Required | The date the span ends; use TODAY() for the span up to today. |
unit | Required | The unit code in quotes: "y" years, "m" months, "d" days, "ym" months beyond whole years, "md" days beyond whole months, "yd" days beyond whole years. |
Examples
1Length of service in years, months and days
The report date is in G2. Column C gives completed years ("y"), column D the months left after the years ("ym") and column E the days left after the months ("md").
=DATEDIF(B2,$G$2,"y")| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Employee | Start Date | Years | Months | Days | Report Date | |
| 2 | Emma | 2-Mar-2015 | 11 | 7 | 29 | 31-Oct-2026 | |
| 3 | Liam | 15-Mar-2020 | 6 | 7 | 16 | ||
| 4 | Olivia | 31-Oct-2023 | 3 | 0 | 0 | ||
| 5 | Noah | 20-Jan-2026 | 0 | 9 | 11 |
- C2 (filled down to C5)
=DATEDIF(B2,$G$2,"y") - D2 (filled down to D5)
=DATEDIF(B2,$G$2,"ym") - E2 (filled down to E5)
=DATEDIF(B2,$G$2,"md")
Liam's service is 6 years 7 months 16 days. Olivia's third anniversary falls on the report date, so her month and day columns are zero: 3 years. Noah has not completed a year yet, so his years column shows 0.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2How many months and days a lease ran
Column D gives completed months ("m") and column E the total number of days ("d"). The end date is written as the last day of the lease.
=DATEDIF(B2,C2,"m")| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Tenant | Start | End | Total Months | Total Days |
| 2 | Flat 3 | 1-Sep-2024 | 31-Aug-2026 | 23 | 729 |
| 3 | Flat 5 | 15-Apr-2025 | 5-Nov-2026 | 18 | 569 |
| 4 | Shop 1 | 1-Feb-2026 | 31-Jul-2026 | 5 | 180 |
- D2 (filled down to D4)
=DATEDIF(B2,C2,"m") - E2 (filled down to E4)
=DATEDIF(B2,C2,"d")
Flat 3 has a two-year lease, yet the result is 23 months: with an end date of 31 August, the 24th month is only completed on 1 September. To count the last day as well, add 1 to the end date: =DATEDIF(B2,C2+1,"m").
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #NUM!
Cause: The start date is after the end date, or the unit code is not valid (e.g. "years").
Fix: Put the dates in the right order and use only "y", "m", "d", "ym", "md" or "yd" as the unit.
- #NAME?
Cause: The unit was typed without quotes (
DATEDIF(A2,B2,y)), so Excel takes y for a name.Fix: Put the unit in quotes:
"y".- #VALUE!
Cause: One of the dates is stored as text and cannot be read.
Fix: Convert the dates to real dates.
Good to know
- To show the span as text in one cell:
=DATEDIF(A2,B2,"y")&" years "&DATEDIF(A2,B2,"ym")&" months". - For the span up to today, pass
TODAY()as the end date; the result updates every time the file opens (TODAY).
In Nexell
Get Nexell to do it from a sentence
Show each employee's length of service from the start date to the report date in three columns: years, months and days
Convert to tableBring your staff list into the tool and type this. Nexell adds the service columns with real Excel formulas and shows them in the preview; in the downloaded file, every span recalculates when you change the report date.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
How do I count the months between two dates in Excel?
=DATEDIF(A2,B2,"m") returns the number of completed months. If you want part months too, =YEARFRAC(A2,B2)*12 gives a result with decimals.
Why is DATEDIF missing from the function list?
DATEDIF is a compatibility function that is not shown in the Insert Function window or in autocomplete. It works when you type its name and arguments into the cell yourself.