Excel Calculate Years Months Days Between Two Dates
This article explains the different ways to calculate the number of years, months, and days between two dates in Microsoft Excel. You will learn the core functions—DATEDIF, YEARFRAC, and simple arithmetic—and how to combine them to get a complete breakdown of the time span. The guide also covers common pitfalls, such as date formatting issues and leap years, and provides clear examples you can adapt to your own spreadsheets.
Understanding Excel Date Systems
Before writing any formula, it helps to know how Excel stores dates. Internally, every date is a serial number: January 1, 1900 is serial number 1, January 2, 1900 is 2, and so on. For example, March 14, 2024 is stored as 45365. This system lets you subtract dates directly to get the number of days between them. However, simply dividing that day count by 30 or 365 gives inaccurate results because months and years have different lengths. That is why dedicated functions like DATEDIF exist.
Excel also has two date systems: the 1900 date system (default on Windows) and the 1904 date system (default on Mac versions before Excel 2016, and still an option). The 1904 system treats January 1, 1904 as day 0. If you share files between Windows and Mac, a date might display differently, but the formulas for years, months, and days work the same way in both systems.
The DATEDIF Function: The Core Tool
DATEDIF is the most direct way to get a difference in whole years, whole months, or whole days. Its syntax is:
=DATEDIF(start_date, end_date, unit)
where unit is a text code that tells Excel what to return:
- “Y” – complete years between the dates.
- “M” – complete months between the dates.
- “D” – complete days between the dates.
- “MD” – days after subtracting whole months (ignores years and months).
- “YM” – months after subtracting whole years (ignores years and days).
- “YD” – days after subtracting whole years (ignores years, treats dates as if in the same year).
For example, if cell A1 contains 2021-01-15 and B1 contains 2024-03-10, then:
=DATEDIF(A1, B1, "Y")returns 3 (three full years from Jan 15, 2021 to Jan 15, 2024).=DATEDIF(A1, B1, "YM")returns 1 (one full month from Jan 15 to Feb 15, 2024).=DATEDIF(A1, B1, "MD")returns 24 (days from Feb 15 to Mar 10, 2024).
Notice that DATEDIF counts complete periods. It ignores the start day when counting years and months, and then uses the “MD” unit to get the leftover days. This gives a clean breakdown of years, months, and days.
Combining Units for a Full Breakdown
To get the classic “X years, Y months, Z days” result, you need three separate DATEDIF formulas. The trick is to use the start date as the reference for the first two, and then a modified start date for the days. A common approach is:
=DATEDIF(A1, B1, "Y") & " years, " & DATEDIF(A1, B1, "YM") & " months, " & DATEDIF(A1, B1, "MD") & " days"
This works because DATEDIF with “YM” gives the months remaining after full years, and “MD” gives the days remaining after full months. For the example above, it returns “3 years, 1 months, 24 days”. Note that if the start date is the last day of a month, Excel may produce unexpected results. For instance, from January 31 to February 28 (non-leap year), DATEDIF with “MD” returns 0, because it treats the end of February as the equivalent of January 31 in month counting. This is a known quirk; if you need calendar-accurate days, you may need a different approach.
Using YEARFRAC for Decimal Years
Sometimes you don’t want a text breakdown but a single number representing the elapsed time in years, such as for age calculations or service anniversaries. The YEARFRAC function returns the fraction of a year between two dates, using a day-count basis. Its syntax is:
=YEARFRAC(start_date, end_date, [basis])
The optional basis argument controls how days are counted:
- 0 or omitted – US (NASD) 30/360: assumes 30-day months and 360-day years.
- 1 – Actual/actual: uses the actual number of days in each month and year.
- 2 – Actual/360: actual days but assumes a 360-day year.
- 3 – Actual/365: actual days but assumes a 365-day year.
- 4 – European 30/360: like basis 0 but with different rules for month-end dates.
For most general purposes, basis 1 (actual/actual) is the most intuitive. For example, =YEARFRAC(DATE(2021,1,15), DATE(2024,3,10), 1) returns approximately 3.15, meaning about 3.15 years. This is useful for calculating interest or pro-rated amounts, but it does not directly give you whole months and days.
Simple Arithmetic for Days and Beyond
If you only need the total number of days, a plain subtraction works:
=B1 - A1
This returns the number of days between the two dates, because Excel stores dates as serial numbers. To convert that to weeks, divide by 7; to get months, you can divide by 30.4 (average days per month) but that is an approximation. For exact months, use DATEDIF.
You can also use the DATEDIF function with the “D” unit to get the same result: =DATEDIF(A1, B1, “D”). The subtraction method is slightly faster, but DATEDIF is clearer when you want the number of days independent of time formatting.
Common Mistakes and How to Avoid Them
Several issues frequently trip up users when calculating date differences:
- Dates stored as text: If a date is entered as text (e.g., “January 15, 2021”), Excel may not recognize it as a date. Use the DATEVALUE function to convert text to a real date, or ensure cells are formatted as dates.
- Reversed start and end dates: DATEDIF returns an error (#NUM!) if the start date is later than the end date. Always double-check the order.
- Leap years: DATEDIF handles leap years correctly for “Y” and “YM”, but the “MD” unit can give unexpected results around February 29. For example, from February 29, 2020 to February 28, 2021, DATEDIF with “MD” returns 0, even though most people would consider it 365 days.
- Using 30-day months: Dividing total days by 30 or 30.4 gives an approximate number of months, which is fine for rough estimates but not for legal or financial calculations.
- Formatting the result: When you concatenate text with numbers, the numbers may lose their formatting. Use the TEXT function to format, e.g., =DATEDIF(A1,B1,”Y”) & ” years” is fine, but if you want leading zeros, wrap with TEXT.
Alternatives: Array Formulas and LET
In newer versions of Excel (Microsoft 365, Excel 2021), you can use the LET function to make the formula more readable and avoid repeating DATEDIF calls. For example:
=LET(start, A1, end, B1, years, DATEDIF(start, end, "Y"), months, DATEDIF(start, end, "YM"), days, DATEDIF(start, end, "MD"), years & "y " & months & "m " & days & "d")
This defines variables once and then uses them in the final concatenation. It also makes the formula easier to maintain if you change the start or end dates.
Another alternative is to use the NETWORKDAYS function to count only working days (Monday to Friday), which is useful for project timelines. But that counts days, not years or months. For a full breakdown, DATEDIF remains the standard.
Frequently Asked Questions
Why does DATEDIF not appear in the function list?
DATEDIF is a hidden function in Excel, meaning it is not shown in the Insert Function dialog, but it works perfectly when typed manually. It was inherited from Lotus 1-2-3 and is kept for compatibility. You can still use it in any formula; just type it directly.
How do I calculate age in years, months, and days from a birthdate?
Use the same DATEDIF approach with the birthdate as the start date and TODAY() as the end date. For example: =DATEDIF(A2, TODAY(), “Y”) & ” years, ” & DATEDIF(A2, TODAY(), “YM”) & ” months, ” & DATEDIF(A2, TODAY(), “MD”) & ” days”. Note that TODAY() updates automatically each day the workbook is opened.
Can I calculate the difference in years, months, and days between two dates in Google Sheets?
Yes, Google Sheets also supports DATEDIF with the same syntax. The function works identically, so you can copy the formulas from Excel to Sheets without changes.
What is the difference between DATEDIF and YEARFRAC?
DATEDIF returns whole units (years, months, days) as integers, which is ideal for a human-readable breakdown. YEARFRAC returns a decimal number representing the fraction of a year, which is better for calculations like interest accrual or prorated amounts. They serve different purposes.
How do I handle dates that are in the future?
If the end date is in the future, the same formulas work as long as the start date is earlier. If you want to know how long until a future date, use the future date as the end date and today as the start. If the start date is after the end date, DATEDIF will return an error, so you may want to use the ABS function or swap the dates.
Why does my formula return 0 days for dates like Jan 31 to Feb 28?
This is due to how DATEDIF handles month-end dates. It treats the last day of February as the same “day” as January 31 for the purpose of counting months, so the leftover days are zero. If you need the actual number of days, use the simple subtraction method or a custom formula that accounts for calendar days.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.