DATEDIF Function in Excel, Google Sheets, Airtable & Notion
DATEDIF counts the complete years, months or days between a start date and an end date. It is the usual way to calculate an age or length of service.
Syntax in each app
| App | Support | Syntax & notes |
|---|---|---|
| Excel | ✔ Built in | DATEDIF(start_date, end_date, unit)Undocumented in Excel: it works but does not appear in autocomplete or the function list. |
| Google Sheets | ✔ Built in | DATEDIF(start_date, end_date, unit) |
| Airtable | ≈ Workaround | DATETIME_DIFF(end_date, start_date, 'years')Units 'years', 'months', 'days' match Y, M and D. MD, YM and YD are rebuilt from those. Whole months can differ by one when the start is the last day of a long month (31 Jan → 28 Feb counts as 1 month). |
| Notion | ≈ Workaround | dateBetween(end_date, start_date, "years")Units "years", "months", "days" match Y, M and D. MD, YM and YD are rebuilt from those, and month-end edge cases may differ from Excel by one. |
| Excel | =DATEDIF(DATE(1990, 6, 15), DATE(2024, 3, 10), "Y") | open excel.new ↗ |
|---|---|---|
| Google Sheets | =DATEDIF(DATE(1990, 6, 15), DATE(2024, 3, 10), "Y") | open sheets.new ↗ |
| Airtable workaround | DATETIME_DIFF(DATETIME_PARSE("2024-03-10", "YYYY-MM-DD"), DATETIME_PARSE("1990-06-15", "YYYY-MM-DD"), 'years') | |
| Notion workaround | dateBetween(parseDate("2024-03-10"), parseDate("1990-06-15"), "years") |
Excel & Sheets use cell ranges. Paste this sample data into cell A1 of a blank sheet, then paste the formula into any empty cell.
The tester follows Excel, including its MD quirk. Airtable and Notion return a negative number instead of an error when the start is after the end.
Arguments
| Argument | What it means |
|---|---|
start_date | The earlier date. If it is after end_date, Excel and Google Sheets return #NUM!. |
end_date | The later date. |
unit | "Y" whole years, "M" whole months, "D" days, "YM" months left after whole years, "MD" days left after whole months, "YD" days left after whole years. |
Examples
| Formula | Result | Why | Copy |
|---|---|---|---|
=DATEDIF(DATE(1990, 6, 15), DATE(2024, 3, 10), "Y") | 33 | Age on 10 March 2024: the June birthday has not happened yet. | |
=DATEDIF(DATE(1990, 6, 15), DATE(2024, 3, 10), "YM") | 8 | Plus 8 months… | |
=DATEDIF(DATE(1990, 6, 15), DATE(2024, 3, 10), "MD") | 24 | …and 24 days. | |
=DATEDIF(DATE(2020, 1, 31), DATE(2021, 3, 1), "MD") | -2 | The MD bug: Excel returns a negative number here. |
Examples use Excel / Google Sheets syntax unless noted.
How DATEDIF works
DATEDIF is a leftover from Lotus 1-2-3 that Excel still supports but has never documented properly: you will not find it in the function wizard or autocomplete, yet =DATEDIF(A2, TODAY(), "Y") works in every version. Google Sheets documents it fully. It counts complete units only, so someone born on 15 June 1990 is 33 on 10 March 2024 and turns 34 on 15 June. Combine units for a readable duration: =DATEDIF(A2, B2, "Y") & " years, " & DATEDIF(A2, B2, "YM") & " months".
Microsoft itself warns against the MD unit. When the start day is larger than the end day, it borrows the length of the month before the end date, which can produce zero or negative results: from 31 January to 1 March gives −2 in a non-leap year. If you need exact “days left over”, calculate =B2 - EDATE(A2, DATEDIF(A2, B2, "M")) instead. The start date must not be after the end date, otherwise DATEDIF returns #NUM!; for a simple day count that can go negative, use DAYS or =B2 - A2.
Airtable's DATETIME_DIFF(end, start, 'years') and Notion's dateBetween(end, start, "years") are the equivalents. Both put the later date first — the opposite order to DATEDIF — and return a negative number rather than an error if you swap them. Neither has the MD/YM/YD units, so the copy-ready formulas rebuild them from whole months and years; those versions avoid Excel's MD bug, so they can disagree with Excel in exactly the cases where Excel is wrong.
Frequently Asked Questions
- Why can't I find DATEDIF in Excel?
- It is a hidden compatibility function. Type it in full — it works even though autocomplete does not suggest it.
- How do I calculate age from a date of birth?
=DATEDIF(A2, TODAY(), "Y"). In Airtable:DATETIME_DIFF(TODAY(), {Birthday}, 'years'). In Notion:dateBetween(today(), prop("Birthday"), "years").