Excel Age Formula: Years & Months from DOB

This calculator works out a person’s age in years and months from their date of birth, using the standard Excel DATEDIF approach. You enter the birth date and a reference date (usually today), and you get the completed years and the remaining months, exactly as Excel would show them.

Formula

Excel’s DATEDIF function is the classic way to get a full age in years and months:

=DATEDIF(start_date, end_date, "Y") gives the complete years.
=DATEDIF(start_date, end_date, "YM") gives the remaining months after those years.

In plain math, the calculator above approximates this with:

• Years = floor((reference − birth) ÷ 365.25)
• Months = floor((remainder after years) ÷ 30.4375)

Where:

• birth – the date of birth (date)
• reference – the date you measure to (date)
• 365.25 – average days per year, accounting for leap years
• 30.4375 – average days per month (365.25 ÷ 12)

This matches Excel’s result for almost all real dates. The tiny difference is that Excel’s DATEDIF counts actual calendar months, while the average-day method can be off by a day or two near month boundaries.

Worked Example

Using the defaults: birth = 1990-05-15, reference = 2025-04-10.

1. Days between the two dates = 12,748 days.
2. Years = floor(12,748 ÷ 365.25) = floor(34.90) = 34 years.
3. Remainder = 12,748 − (34 × 365.25) = 12,748 − 12,418.5 = 329.5 days.
4. Months = floor(329.5 ÷ 30.4375) = floor(10.83) = 10 months.

So the age is 34 years and 10 months. Excel’s DATEDIF gives the same result: from May 15, 1990 to April 10, 2025, exactly 34 full years, and the remaining months from May 15 to April 10 is 10 (since May 15 to March 15 is 10 months, and the extra days don’t complete an 11th month).

How Excel Actually Calculates Age

Excel stores dates as serial numbers – the number of days since January 1, 1900. So subtracting two dates gives the exact number of days between them. DATEDIF then breaks that into years, months, and days using the calendar, not averages.

For years: it counts how many full anniversaries of the birth date have passed before the end date.
For months: it counts how many full month-steps (e.g., from the 15th to the 15th) fit in the remaining days.

That’s why Excel can return 34 years, 10 months, and 26 days – the days part is the leftover after months. The calculator above gives only years and months, as the title asks, but you can get the days by using DATEDIF(...,"MD") in Excel.

Common Mistakes and Edge Cases

• Using 365 days per year – this ignores leap years and will be wrong for anyone born before a leap day. Always use 365.25 or let Excel handle it.
• Using 30 days per month – this is for financial calculations, not ages. Real months have 28–31 days.
• Leap day births (Feb 29) – Excel treats Feb 29 as Feb 28 in non-leap years. So someone born on Feb 29, 2000 turns 1 on Feb 28, 2001, not Mar 1.
• Rounding vs. truncating – age is always the completed years, not rounded. A child who is 4 years and 11 months is not 5 years old.

How to Use This in Excel

If you have a column of birth dates in Excel, you can add a formula like:

=DATEDIF(A2, TODAY(), "Y") & " years, " & DATEDIF(A2, TODAY(), "YM") & " months"

This returns text like “34 years, 10 months”. If you want separate numeric columns, use DATEDIF twice – once with "Y" and once with "YM".

The TODAY() function updates automatically, so the age stays current. If you need a fixed date, replace TODAY() with a cell reference like $B$1.

Why Use Average Days Instead of Calendar Months?

The calculator uses average days per month (30.4375) because it’s a simple, transparent formula that anyone can verify. Excel’s calendar-based method is more precise but harder to replicate without a date library.

For most practical purposes – forms, eligibility, milestones – the difference is at most a day or two, and only when the reference date is near the end of a month. If you need exact calendar months, use Excel’s DATEDIF directly.

Limits of the Formula

• Date format – the calculator expects a date in YYYY-MM-DD format. If you enter a different format, it may misinterpret the values.
• Reference date before birth – the result will be negative, which is meaningless. Ensure the reference is after the birth date.
• Very old dates – Excel’s date system starts at Jan 1, 1900, so dates before that are not valid in Excel. The calculator above will still work for older dates, but Excel won’t.
• Leap year boundary – for a birth on Feb 29, the average-day method may give slightly different months than Excel. Use Excel for legal or medical contexts.

Frequently Asked Questions

How do I get the age in Excel without DATEDIF?

You can use =INT((TODAY()-A2)/365.25) for years, but this is less accurate near birthdays. For months, use =INT(MOD((TODAY()-A2),365.25)/30.4375). It works but can be off by a day or two. DATEDIF is the reliable way.

Why does DATEDIF not appear in Excel’s function list?

DATEDIF is a hidden compatibility function. It works in all modern Excel versions, but you must type it manually – it won’t show up in the autocomplete list. That’s why many people don’t know it exists.

What is the difference between “Y” and “YM” in DATEDIF?

“Y” returns the total full years. “YM” returns the remaining months after those full years, ignoring the years. For example, for someone who is 34 years and 10 months old, “Y” gives 34 and “YM” gives 10.

Can I calculate age in years, months, and days?

Yes. Use three DATEDIF formulas: "Y" for years, "YM" for months after years, and "MD" for days after months. Combine them with text: =DATEDIF(A2,TODAY(),"Y")&"y "&DATEDIF(A2,TODAY(),"YM")&"m "&DATEDIF(A2,TODAY(),"MD")&"d".

Why does my age in Excel change by one day at the end of the month?

Because DATEDIF counts calendar months. If you were born on March 31, Excel counts a full month on April 30 (not April 31), so your age in months increments on the 30th. That’s correct calendar behavior, but it can surprise people used to 30-day months.

Is there a way to round age up to the nearest year?

Yes, use =ROUNDUP(DATEDIF(A2,TODAY(),"Y") + (DATEDIF(A2,TODAY(),"YM")/12), 0) or simply =ROUNDUP((TODAY()-A2)/365.25, 0). But note that rounding up is rarely what you want – age is normally the completed years.

Related

• Mastodon Server on VPS: Docker Setup Guide
• GTA 6 Day Night Cycle: Fans Calculate Length
• Grey’s Anatomy Watch Order: Series & Spin-Offs

in dob | Date of birth | - | 1990-05-15 | 0
in ref | Reference date | - | 2025-04-10 | 0
out years | Years | yr | floor((ref-dob)/365.25) | 0
out months | Remaining months | mo | floor(mod(ref-dob,365.25)/30.4375) | 0