Skip to content

Excel function · Date & time

Excel DATEDIF function

DATEDIF returns the time between two dates in completed years, months or days: an employee's length of service, how many months a contract ran. The unit codes are short letters in quotes: "y", "m", "d".

In Turkish Excel: ETARİHLİ

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

English Excel=DATEDIF(start_date, end_date, unit)
Turkish Excel=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

ArgumentRequiredDescription
start_dateRequiredThe date the span starts. It cannot be later than the end date.
end_dateRequiredThe date the span ends; use TODAY() for the span up to today.
unitRequiredThe 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").

C2fx=DATEDIF(B2,$G$2,"y")
Length of service in years, months and days: example table
ABCDEFG
1EmployeeStart DateYearsMonthsDaysReport Date
2Emma2-Mar-20151172931-Oct-2026
3Liam15-Mar-20206716
4Olivia31-Oct-2023300
5Noah20-Jan-20260911
  • 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.

D2fx=DATEDIF(B2,C2,"m")
How many months and days a lease ran: example table
ABCDE
1TenantStartEndTotal MonthsTotal Days
2Flat 31-Sep-202431-Aug-202623729
3Flat 515-Apr-20255-Nov-202618569
4Shop 11-Feb-202631-Jul-20265180
  • 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

Type this in Nexell

Show each employee's length of service from the start date to the report date in three columns: years, months and days

Convert to table

Bring 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.

Related functions

All date & time functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free