Excel Formula: Years, Months, and Days Between Dates

To calculate complete years, months, and remaining days between dates in Excel, use DATEDIF for the years and months, then subtract the date reached with EDATE to get the leftover days. With a start date in A2 and an end date in B2, the formulas below give a clear, calendar-based breakdown.

Set up the dates first

Enter the earlier date in A2 and the later date in B2. Excel needs to recognize both entries as real dates, not text that merely looks like a date. Select the cells and choose a date format from Home > Number Format if their display is unclear. Formatting changes how a date appears, not the underlying value used in calculations.

Excel stores dates as serial numbers, which lets it subtract one date from another. Date interpretation can depend on your regional settings: for example, 04/05/2025 could mean April 5 or May 4. If a date is ambiguous, enter it using a format that is clear in your Excel setup, or use Excel’s date picker where available. A date pasted from a website or imported file may be stored as text; a formula may then return an error or an unexpected result.

This method assumes you want a nonnegative interval from the earlier date to the later date. If B2 is before A2, swap the dates or decide how you want to represent a negative interval. DATEDIF does not provide a signed years-months-days result for reversed dates.

Calculate each part in its own cell

Separate result cells are easiest to audit and reuse. Put the number of complete years in C2. With the start date in A2 and end date in B2, enter:

=DATEDIF(A2,B2,"Y")

For the remaining complete months, first move the start date forward by the years already counted. With A2 and B2 holding the dates and C2 holding the years result, enter in D2:

=DATEDIF(EDATE(A2,12*C2),B2,"M")

Finally, calculate the days after those complete years and months. With the same dates, C2 containing years, and D2 containing months, enter in E2:

=B2-EDATE(A2,12*C2+D2)

For example, if A2 is 15-Feb-2020 and B2 is 04-Jun-2023, C2 returns 3. The date three years after the start is 15-Feb-2023; D2 returns 3 complete months, ending at 15-May-2023; E2 returns 20 days. The result is 3 years, 3 months, and 20 days. This decomposition counts calendar anniversaries rather than treating a month as a fixed number of days.

Show the result in one cell

If the separate values are in C2:E2 and the dates are in A2:B2, join the results with this formula in another cell:

=C2&" years, "&D2&" months, "&E2&" days"

That produces readable text such as “3 years, 3 months, 20 days.” Because the result is text, it is convenient for a report or label but cannot be used directly as a numeric duration in further arithmetic. Keep the component cells if you need to sort, total, or analyze the values.

Microsoft 365 and Excel 2021 also support LET, which can calculate the components inside one formula without helper cells. With the start date in A2 and end date in B2, enter:

=LET(yrs,DATEDIF(A2,B2,"Y"),mos,DATEDIF(EDATE(A2,12*yrs),B2,"M"),remDays,B2-EDATE(A2,12*yrs+mos),yrs&" years, "&mos&" months, "&remDays&" days")

LET assigns names to intermediate values, so the logic is easier to follow than a long formula that repeats the same calculations. The formula returns text, just like the joined-cell version. In older Excel versions, use the separate-cell method instead.

How DATEDIF counts intervals

DATEDIF returns the difference between two dates in a specified unit. Its unit argument is text in quotation marks. The units most relevant to date breakdowns are:

"Y" returns complete years between the dates.
"M" returns complete months between the dates.
"D" returns the total number of days.
"YM" returns remaining months after complete years are excluded.
"YD" returns days after complete years are excluded.
"MD" returns days after both complete months and years are excluded.

The distinction between total units and leftover units matters. For example, “total months” can be a useful measure for a service period, but it is not the same as “months remaining after full years.” Similarly, total days is an elapsed-day count, not the days component of a years-months-days breakdown.

DATEDIF is retained for compatibility with older spreadsheet programs, but Excel may not show it in formula autocomplete or provide the same level of formula guidance as for newer functions. It is still usable when entered with valid arguments. The EDATE-based approach above avoids relying on the MD unit for the final days, which Microsoft cautions can produce inaccurate results in some situations.

Why not use YM and MD for every component?

A tempting shortcut is to calculate years with the Y unit, months with YM, and days with MD, then join the three results. This can work for many ordinary date pairs, but MD has known edge cases, especially around dates at the end of a month. The final number may not be the days left after the exact years-and-months date reached from the starting date.

The anchored approach makes the order explicit: count complete years, move the start date forward by those years, count complete months from that new date, then subtract the date reached after both components. EDATE is useful because it moves a date by a specified number of months and adjusts when the destination month has fewer days. For example, moving a date on the 31st into a shorter month results in the last valid day of that month.

There is no single universal definition of a “month” as a duration: calendar months vary in length. For age, tenure, or a contract anniversary, a calendar-based breakdown is often the intended meaning. For billing or scientific work, the governing rules may instead define fixed day counts, inclusive counting, or a particular convention. Match the spreadsheet method to the question being answered.

Choose the right calculation for the question

Before selecting a formula, decide whether you need a calendar breakdown or a single elapsed-time measure. These outputs answer different questions:

Years, months, and days: Use the separate-cell method when readers need to understand a span in calendar units, such as age or time since a start date.
Total days: Subtract the start date from the end date when the actual number of elapsed date boundaries is what matters. With the start date in A2 and end date in B2, use =B2-A2. For 15-Feb-2020 through 04-Jun-2023, this returns 1,205 days.
Total complete months: Use DATEDIF with the M unit when you need completed months overall, rather than a months remainder within a years-months-days result.
Decimal years: A decimal-year calculation is not the same as calendar years plus leftover months and days. It requires a day-count convention, so use it only when that convention is defined.

Also decide whether the end date is included. Standard date subtraction measures the difference between date serials; it does not add an extra day merely because someone describes a period as “from Monday through Wednesday.” If a process counts both the first and last calendar dates, that is an inclusive-counting rule and should be applied deliberately. Do not silently add one day to an age or anniversary calculation.

Common errors and edge cases

#NUM!: DATEDIF commonly returns this when the start date is later than the end date. Confirm the cell order and that both cells contain valid dates.
#VALUE! or an unexpected result: One or both entries may be text rather than dates. Re-enter them as dates or convert the imported values before calculating.
Unexpected month or day near month-end: Calendar months have different lengths. EDATE adjusts a destination date when the original day number does not exist in the target month; this is normal, but it may differ from a company’s own anniversary policy.
Leap-day birthdays or anniversaries: February 29 does not occur every year. Excel’s date functions apply date arithmetic rules, but a policy may define the anniversary as February 28 or March 1 in non-leap years. Confirm the intended rule rather than assuming the spreadsheet decides it.
Times included with dates: Excel can store a time as a fractional part of a day. If cells contain timestamps but the question is about whole calendar dates, remove or ignore the time portions consistently; otherwise, a day boundary or displayed value may surprise you.

To inspect a result, temporarily format the component cells as General or Number and compare them with the dates reached by the year and month steps. This can reveal whether a problem is caused by date interpretation, an unexpected time value, or the chosen anniversary convention.

Formatting and reusing the results

The separate-cell approach is useful when a workbook contains many records. Enter the formulas in C2:E2, then fill them down for additional rows. Each row should use its own start and end date cells. Keep the component formulas numeric, and apply number formatting if you want leading zeroes for months or days; do not turn the underlying values into text unless the output is only for display.

For a compact report, combine the components in a display column while retaining the numeric columns for calculations. You can also change the labels to singular or plural with extra logic, but that adds complexity and is not necessary for accurate arithmetic. If a report must say “1 year, 1 month, 1 day,” review the wording separately from the numeric calculation.

On Excel for Mac, the formulas use the same function names and comma-separated arguments in typical English-language installations. Regional settings can use different list separators or date-entry conventions, so follow the separator Excel expects if it rejects a copied formula. Excel for the web also supports these date calculations; the menus and display may differ slightly from the Windows desktop interface.

Frequently Asked Questions

What is the formula for years, months, and days between two dates in Excel?
Use DATEDIF to count complete years, use EDATE and DATEDIF to count the remaining complete months, then subtract the date reached to get the remaining days. The separate-cell formulas above are easier to check than a single long expression.

Can DATEDIF calculate years, months, and days in one formula?
Yes. A LET formula can name and combine the intermediate results in Microsoft 365 or Excel 2021. In older versions, calculate each component in its own cell and join them if you need a single display string.

Why does DATEDIF not appear in Excel’s formula suggestions?
DATEDIF is maintained for compatibility and may not appear in autocomplete or function suggestions. Enter the function and its arguments directly; the absence from the suggestions does not by itself mean the function is unavailable.

Should I use DATEDIF with YM and MD?
YM can provide the month remainder after complete years. MD can be convenient, but it has edge cases and Microsoft advises caution. For a more transparent day remainder, calculate the date reached after the years and months with EDATE, then subtract it from the end date.

How do I calculate total days instead of years, months, and days?
Subtract the earlier date from the later date. That returns elapsed days between the date serials. If your process counts both endpoints, apply that inclusive rule explicitly rather than confusing it with ordinary elapsed-day subtraction.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.