How to Calculate Years, Months, and Days in Excel (Complete Guide)

Accurately breaking down a time span into years, months, and days is a frequent Excel need — for ages, service periods, or project durations. Below you'll find multiple reliable formulas, including two combined formulas you asked to include.

The Basics

Excel stores dates as serial numbers (1 = Jan 1, 1900). Simple subtraction (B2 - A2) returns total days, but to express results as Years, Months, Days we use functions like DATEDIF, YEARFRAC, and text concatenation.

Core Functions

• DATEDIF(start_date, end_date, unit) — hidden but reliable. Units include "Y", "M", "D", "YM", "MD", "YD".
• YEARFRAC(start_date, end_date) — returns fractional years (useful when you want decimal/approximate years).
• TODAY() — useful when calculating age as of today.

Recommended Pieces

Use DATEDIF to get exact whole years, remaining months and remaining days:

=DATEDIF(A2, B2, "Y")      ' full years
=DATEDIF(A2, B2, "YM")     ' remaining months after years removed
=DATEDIF(A2, B2, "MD")     ' remaining days after months removed

Smart Combined Duration Formula (handles commas & zero years/days)

This is the formula you previously added; it shows Years only if > 0, always shows Months, and shows Days only if > 0 — with commas placed correctly.

=IF(DATEDIF(A2,B2,"Y")>0,DATEDIF(A2,B2,"Y") & " Years","")
& IF(DATEDIF(A2,B2,"Y")>0,", ","")
& DATEDIF(A2,B2,"YM") & " Months"
& IF(DATEDIF(A2,B2,"MD")>0,", ","")
& IF(DATEDIF(A2,B2,"MD")>0,DATEDIF(A2,B2,"MD") & " Days","")

Note: This version always includes the months portion (even when months = 0). It avoids stray commas when Years or Days are absent.

Alternative: YEARFRAC + DATEDIF (your new formula)

You asked to add this formula too. It uses YEARFRAC to compute whole years (via INT) and then uses DATEDIF for months and days. You added + 1 to the MD result — often used to make the day-count inclusive (i.e., counting both start and end date). Use it if inclusive counting is what you want.

=INT(YEARFRAC(A2, B2)) & " Years, " & DATEDIF(A2, B2, "YM") & " Months, " & (DATEDIF(A2, B2, "MD") + 1) & " Days"

Important: YEARFRAC approximates years (it depends on the basis Excel uses). Using INT(YEARFRAC(...)) usually matches DATEDIF(...,"Y") for whole years, but in some edge cases (leap years or exact cutoffs) results may differ slightly. The + 1 on days makes the days count inclusive — remove the + 1 if you prefer exclusive counting.

When to Use Which Formula

Goal	Best Formula	Why
Precise calendar breakdown (years, remaining months, remaining days)	DATEDIF-based combined formula (first one)	Exact and reliable for whole-year/month/day components.
Prefer YEARFRAC-style years (fractional/approximate) but display whole years	The INT(YEARFRAC(...)) formula (second one)	Uses fractional-year logic then truncates to whole years; useful when you need YEARFRAC for other calculations.
Inclusive day counting (count both start and end)	Second formula with + 1 on days	Makes the days portion inclusive.

Examples (formats you'll get)

• 3 Years, 2 Months, 5 Days
• 4 Months (when years = 0 and days = 0 — first formula will display 4 Months)
• 1 Years, 0 Months, 12 Days — you can tweak to hide "0 Months" if you want a cleaner output (see next section).

Optional: Hide Zero Months / Singular/Plural tweaks

If you want to hide 0 Months or convert 1 Years to 1 Year, you can wrap parts with conditional IF tests and use IF(value=1," Year"," Years") style logic. If you'd like, I can provide a version that automatically converts singular/plural and omits zero-value parts.

Troubleshooting

• #NUM! — this occurs when the start date is after the end date. Swap them or validate input before using DATEDIF.
• DATEDIF not listed in function list — it's a hidden function but works when typed manually.
• Different results between formulas — check whether you want inclusive/exclusive counting and whether fractional-years rounding matters (YEARFRAC vs DATEDIF).

Want me to: 1) produce a copy of this content as an HTML file you can download, 2) add singular/plural handling, or 3) produce an Excel file with these formulas and sample dates? Tell me which and I'll generate it right away.

— Happy spreadsheeting!