In Excel: use =DATEDIF(start_date, end_date, "d") — it returns the difference between two dates in the unit you ask for: "d" for days, "m" for whole months or "y" for whole years.
On this page8
Syntax
Arguments
| Argument | required / optional | Description |
|---|---|---|
start_date | required | The earlier date — the start of the period. |
end_date | required | The later date — the end of the period; it must not be earlier than start_date. |
unit | required | The unit in quotes: "d" days, "m" whole months, "y" whole years, plus "ym", "yd" and "md" for the leftover part. |
Related functions
Put the earlier date in one cell and the later date in another, such as A2 and B2.
In the result cell type =DATEDIF( and click the start date, then a comma and the end date.
Type a comma and the unit in quotes: "y" for years, "m" for months, or "d" for days.
Close the bracket and press Enter; for an age, use =DATEDIF(A2, TODAY(), "y").
For a detailed breakdown, concatenate "y", "ym" and "md" results into one text string.
What this does
DATEDIF returns the gap between two dates measured in whole days, months or years, depending on a unit code you give in quotes. Beyond the basic "d", "m" and "y", three combination codes return only the remainder: "ym" the months ignoring whole years, "yd" the days ignoring whole years, and "md" the days ignoring whole months. It is most famous for age calculation — =DATEDIF(DOB, TODAY(), "y") gives a person's age in completed years. DATEDIF is a legacy function kept so Excel can open older Lotus 1-2-3 workbooks. Microsoft documents it, but it is absent from Excel's Formulas ribbon function list and gives no autocomplete, so you type it in full. One caveat from that documentation: Microsoft advises against the "md" unit, which has known limitations and can return a negative number, a zero or a plainly wrong result — use it only where you can check the answer, and for a plain day count prefer =B2-A2 or =DAYS(B2, A2). Notably the unit codes stay as the English letters "y", "m" and "d" even in German Excel. The same idea underpins a lot of everyday Excel work, so the few minutes spent getting it right here pay back across every sheet you build afterwards. Treat it as a pattern, not a one-off, and it stops being something you look up and starts being something you reach for. Treat “excel calculate date difference” as a small repeatable workflow rather than a one-off click you hope to remember next time. Use a small test block before the live file, so any surprise in the affected cells shows up while it is still harmless. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. That turns a calculation you can defend to a CFO or an auditor into a method you can reuse, explain, and defend when the workbook leaves your screen.
A worked example
A start date 15/03/2020 sits in A2 and an end date 03/06/2026 in B2. =DATEDIF(A2, B2, "y") returns 6 (whole years), =DATEDIF(A2, B2, "m") returns 74 (whole months), and =DATEDIF(A2, B2, "d") returns 2271 (days). For an exact age string, combine units: =DATEDIF(A2, B2, "y") & " years, " & DATEDIF(A2, B2, "ym") & " months" returns "6 years, 2 months". For a live age, use TODAY() as the end date. DATEDIF is the simplest way to get a clean whole-number difference in years, months or days — the standard tool for ages, tenure and durations across every Excel version. Check the referenced cells, argument order and result type in your workbook. Start with the worked example before changing an input, then compare the changed result with the calculation you expected. An error value or a result of a different type is a reason to inspect the inputs and arguments.
In Google Sheets
Google Sheets support for this task has not been verified. Check the function, arguments and workflow in the target product before transferring the example. Matching formula notation does not establish identical behaviour. Nothing on this page is behind a login or a download: the exact answer is at the top, and the detail below it is there for when you need it. That is the whole promise here — the answer first, and just enough context to make it stick. For “excel calculate date difference”, keep the input cells and the result visible while you check the formula in Excel. Follow the worked example and change one input at a time. The steps explain the workbook calculation; this page does not run a live calculator for that task.
Common mistakes
- Putting the dates in the wrong order — start_date must be the earlier date, or
DATEDIFreturns a#NUM!error. - Expecting "d" to count only working days —
DATEDIFcounts every calendar day; for weekdays useNETWORKDAYSinstead. - Translating the unit codes in German Excel — they stay as the English letters "y", "m" and "d"; only the argument separator changes to a semicolon.
Frequently asked questions
Why is DATEDIF not in the function list?
It is a legacy function kept for Lotus 1-2-3 compatibility, so Excel offers no autocomplete or wizard for it and you must type it in full. Microsoft does document it — and advises against the "md" unit, which can return a negative number, a zero or a wrong result.
How do I calculate age with DATEDIF?
Use the "y" unit with TODAY() as the end date: =DATEDIF(DOB, TODAY(), "y") returns the age in completed years. Add "ym" and "md" parts if you want years, months and days.
Do the unit codes change in German Excel?
No. The unit codes stay as the English letters "y", "m" and "d" even in a German installation; the function name also stays DATEDIF, and only the argument separator becomes a semicolon: =DATEDIF(A2; B2; "y").
Other ways people ask this
This is also commonly searched as “how to calculate date difference in excel”, “calculate difference in dates excel”, “how to calculate difference in dates in excel” and “calculate the date difference in excel”. They describe the identical operation, so you are in the right place no matter how you phrased it.
Why do people search for this in so many different ways?
Because the same task has many names. “how to calculate date difference in excel”, “calculate difference in dates excel”, “how to calculate difference in dates in excel” all point at the one operation explained on this page, which is why they all lead here.