DATEDIF returns the difference between a start date and an end date in whole years, months, or days. It is ideal for calculating ages, lengths of service, and elapsed time between two dates.
=DATEDIF(start_date, end_date, unit)What does DATEDIF do?
DATEDIF is a hidden but powerful date function in Excel. It calculates the difference between two dates and expresses the result in complete years, months, or days. Unlike simple subtraction, DATEDIF understands calendar boundaries, so it can tell you that someone is 34 years old even though their birthday is later this year.
This matters because many everyday tasks depend on accurate date math: HR teams track employee tenure, schools calculate student ages, and project managers measure elapsed time. DATEDIF handles these cases cleanly, avoiding manual counting or complex formulas.
For example, if you have a list of hire dates and need to know how many full years each employee has worked, DATEDIF with the unit "Y" gives the answer in one step. It also supports months and days, and even combined units like "YM" for remaining months after full years.
When to use DATEDIF
- Calculating a person's age from their date of birth.
- Determining employee tenure or length of service in years and months.
- Finding the number of full months between a start date and today.
- Measuring project duration in days, months, or years.
- Calculating remaining days until a deadline or due date.
- Working out subscription or warranty periods in months.
DATEDIF syntax
=DATEDIF(start_date, end_date, unit)| Argument | Required? | What to enter |
|---|---|---|
start_date | Required | The beginning date. Must be a valid Excel date or a reference to a cell containing a date. Example: A2 where A2 holds 2020-01-15. |
end_date | Required | The ending date. Must be on or after start_date. Example: B2 where B2 holds 2024-03-10. |
unit | Required | The unit of time to return. Use "Y" for complete years, "M" for complete months, "D" for days, "MD" for days ignoring months and years, "YM" for months ignoring days and years, or "YD" for days ignoring years. |
What it returns: DATEDIF returns a whole number representing the difference between the two dates in the specified unit. The result is always a positive integer or zero; if start_date is after end_date, it returns #NUM!.
DATEDIF examples
1 Calculate age in years
This example finds the age of each person as of a fixed date. The formula =DATEDIF(A2, B2, "Y") compares the birth date in A2 with the reference date in B2 and returns the number of complete years between them. For the first row, from 1990-05-15 to 2024-03-10, the person has not yet had their birthday in 2024, so the result is 33. The formula is filled down for all rows.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Birth Date | Reference Date | |||
| 2 | Alice | 1990-05-15 | 2024-03-10 | 33 | ||
| 3 | Bob | 1985-11-20 | 2024-03-10 | |||
| 4 | Carol | 2000-01-01 | 2024-03-10 | |||
| 5 | David | 1978-07-04 | 2024-03-10 | |||
| 6 | Eve | 1995-12-31 | 2024-03-10 | |||
| 7 |
2 Find full months between order and delivery
Here we calculate how many complete months passed between an order date and its delivery date. The formula =DATEDIF(A2, B2, "M") counts full calendar months. For an order on 2023-01-10 delivered on 2023-04-05, the difference is 2 full months because the day-of-month in April (5) is before the day in January (10). This helps measure fulfilment speed in whole months.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order ID | Order Date | Delivery Date | |||
| 2 | 1001 | 2023-01-10 | 2023-04-05 | 2 | ||
| 3 | 1002 | 2023-02-15 | 2023-06-20 | |||
| 4 | 1003 | 2023-03-01 | 2023-03-31 | |||
| 5 | 1004 | 2023-04-10 | 2023-09-15 | |||
| 6 | 1005 | 2023-05-20 | 2023-08-19 | |||
| 7 |
3 Calculate days remaining until a deadline
This example shows how many days are left from today's date to a project deadline. Using =DATEDIF(B2, C2, "D") where B2 is today and C2 is the deadline, we get the total days between them. For a deadline of 2024-12-31 and today of 2024-03-10, there are 296 days remaining. This is useful for countdowns and reminders.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Project | Today | Deadline | |||
| 2 | Website | 2024-03-10 | 2024-12-31 | 296 | ||
| 3 | Report | 2024-03-10 | 2024-06-30 | |||
| 4 | Audit | 2024-03-10 | 2024-05-15 | |||
| 5 | Launch | 2024-03-10 | 2024-09-01 | |||
| 6 | Review | 2024-03-10 | 2024-04-01 | |||
| 7 |
4 Employee tenure in years and months
To show tenure as "X years and Y months", we combine two DATEDIF calls. =DATEDIF(B2, C2, "Y") gives full years, and =DATEDIF(B2, C2, "YM") gives the remaining months after subtracting whole years. For a hire date of 2015-03-01 and today of 2024-03-10, the result is 9 years and 0 months. The formula concatenates them with text.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Employee | Hire Date | Today | |||
| 2 | Frank | 2015-03-01 | 2024-03-10 | 9 years, 0 months | ||
| 3 | Grace | 2018-07-15 | 2024-03-10 | |||
| 4 | Henry | 2020-01-20 | 2024-03-10 | |||
| 5 | Ivy | 2012-11-05 | 2024-03-10 | |||
| 6 | Jack | 2019-09-30 | 2024-03-10 | |||
| 7 |
5 Warranty expiry in months
This example calculates how many full months of warranty remain from the purchase date to the warranty end date. The formula =DATEDIF(B2, C2, "M") returns the total months between the two dates. For a purchase on 2022-01-15 and warranty end on 2024-01-14, the result is 23 months because the end date is one day short of the full 24th month. This helps customers know their coverage period.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Purchase Date | Warranty End | |||
| 2 | Laptop | 2022-01-15 | 2024-01-14 | 23 | ||
| 3 | Printer | 2021-06-10 | 2024-06-09 | |||
| 4 | Monitor | 2023-03-20 | 2026-03-19 | |||
| 5 | Keyboard | 2020-12-01 | 2023-11-30 | |||
| 6 | Mouse | 2022-09-05 | 2025-09-04 | |||
| 7 |
Common DATEDIF errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #NUM! | The start_date is later than the end_date. | Ensure the start_date is earlier than or equal to the end_date. Swap the arguments if needed. |
| #VALUE! | One or both dates are not valid Excel dates (e.g., text that looks like a date but is not recognised). | Convert the text to a real date using DATEVALUE or enter the date in a recognised format. |
| #NAME? | The function name is misspelled or the unit is not in double quotes. | Check spelling: DATEDIF. Ensure the unit is a text string like "Y", "M", or "D". |
| #VALUE! | The unit argument is not one of the allowed codes (Y, M, D, MD, YM, YD). | Use only the supported unit codes in uppercase and inside double quotes. |
Tips for using DATEDIF
- DATEDIF is not listed in Excel's function wizard, so type it manually or use the formula bar.
- Use "YM" to get remaining months after full years, and "MD" for remaining days after full months.
- To calculate age as of today, use
=DATEDIF(A2, TODAY(), "Y"). - Remember that DATEDIF counts complete units, so a difference of 1 month and 29 days returns 1 for "M".
- If you need the result as a number for further calculations, avoid concatenation and use separate DATEDIF calls.
- Test edge cases like month-end dates; DATEDIF handles them but may surprise you with "MD" results.
Frequently asked questions
Why is DATEDIF not in the function list?
DATEDIF is a hidden function kept for compatibility with Lotus 1-2-3. It still works in all modern Excel versions but is not documented in the function wizard.
Can DATEDIF calculate age in months?
Yes. Use the unit "M" to get total complete months between two dates, or "YM" for months remaining after full years.
What does the "MD" unit do?
"MD" returns the difference in days, ignoring months and years. For example, from 2024-01-15 to 2024-03-10, it returns 24 days (the days remaining after subtracting full months).
Does DATEDIF work with times?
DATEDIF works with dates only. If you include times, they are ignored and only the date portion is used.
How do I handle negative results?
DATEDIF returns #NUM! if start_date is after end_date. To avoid errors, use an IF statement to check the order or swap the dates.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. DATEDIF reference