Excel Calculate Age Between Two Dates: Years, Months, Days

To calculate a person’s age between two dates in Excel showing years, months, and days, use the DATEDIF function with three separate formulas: =DATEDIF(start_date, end_date, "Y") for years, =DATEDIF(start_date, end_date, "YM") for remaining months, and =DATEDIF(start_date, end_date, "MD") for remaining days. You enter the start date (e.g., birth date), the end date (e.g., today), and Excel returns the full age broken into those three components.

Formula

The age in years, months, and days is calculated using Excel’s DATEDIF function, which returns the difference between two dates in a specified unit. The formula uses three parts:

• =DATEDIF(start_date, end_date, "Y") — complete years between the two dates.
• =DATEDIF(start_date, end_date, "YM") — months left over after counting whole years (ignores years and days).
• =DATEDIF(start_date, end_date, "MD") — days left over after counting whole months (ignores years and months).

Each argument is defined as:

• start_date — the earlier date (e.g., birth date), entered as a cell reference, date serial number, or DATE() function.
• end_date — the later date (e.g., today), must be greater than start_date.
• unit — a text code in quotes: “Y” for years, “YM” for months ignoring years, “MD” for days ignoring months and years.

The result is a whole number for each part. For example, if start_date is 1990-05-15 and end_date is 2025-01-01, the formulas give 34 years, 7 months, and 17 days.

Worked Example

Using the default values from the calculator above: start date 1990-05-15, end date 2025-01-01.

1. Years: =DATEDIF("1990-05-15", "2025-01-01", "Y") → 34 (because 34 full years pass from 1990-05-15 to 2024-05-15).
2. Months: =DATEDIF("1990-05-15", "2025-01-01", "YM") → 7 (from 2024-05-15 to 2025-01-01 is 7 months).
3. Days: =DATEDIF("1990-05-15", "2025-01-01", "MD") → 17 (from 2024-12-15 to 2025-01-01 is 17 days).

So the age is 34 years, 7 months, and 17 days. The calculator block above gives the same result: years = 34, months = 7, days = 17.

When to Use This Calculation

This three-part age calculation is useful whenever you need a precise age in human-readable form, not just a decimal number of years. Common situations include:

• HR and payroll systems that track employee tenure in years, months, and days.
• Medical or insurance forms that ask for exact age at a certain date.
• Legal documents where age must be stated precisely (e.g., contracts, benefits).
• Personal tracking of a child’s age or the duration of a project.

For most purposes, the DATEDIF method is preferred over dividing days by 365.25 because it accounts for calendar months and leap years correctly, giving an age that matches how people actually state their age.

How to Use DATEDIF in Excel

DATEDIF is a hidden function in Excel—it does not appear in the function list, but it works if you type it manually. To use it:

1. Enter your start date in a cell (e.g., A1) and end date in another cell (e.g., B1).
2. In a third cell, type =DATEDIF(A1, B1, "Y") and press Enter.
3. Repeat for months and days using “YM” and “MD”.
4. To combine into one text string, use: =DATEDIF(A1,B1,"Y")&" years, "&DATEDIF(A1,B1,"YM")&" months, "&DATEDIF(A1,B1,"MD")&" days".

Make sure the end date is always later than the start date; otherwise, DATEDIF returns a #NUM! error. Also, dates should be entered as actual dates (not text) for best results.

Common Mistakes and How to Avoid Them

Several errors frequently occur when calculating ages in Excel:

• Using the wrong unit code — “Y” vs. “YM” vs. “MD” are easy to mix up. Remember: “Y” gives total years, “YM” gives months after years, “MD” gives days after months.
• Reversing start and end dates — DATEDIF requires start_date ≤ end_date. If you swap them, you get a #NUM! error.
• Entering dates as text — If you type “May 15, 1990” as text, Excel may not recognize it as a date. Use DATE(1990,5,15) or a date-formatted cell.
• Expecting a decimal age — DATEDIF returns whole numbers. If you need a decimal (e.g., 34.5 years), use =(end_date - start_date)/365.25 instead.
• Ignoring leap years — Dividing by 365.25 is an approximation; DATEDIF handles actual calendar days, so it’s more accurate for age.

Always test with a known date pair (like the example above) to verify your formulas.

Alternatives to DATEDIF

If DATEDIF is not available or you prefer other methods, you can calculate age using:

• YEARFRAC — =INT(YEARFRAC(start, end)) gives whole years, but not months/days separately.
• DATEDIF with TODAY() — For age as of today, use =DATEDIF(A1, TODAY(), "Y") and similar.
• Manual arithmetic — Subtract dates and use MOD to extract months and days, but this is more complex and error-prone.
• LAMBDA function (Excel 365) — You can define a custom function to output a formatted age string, but it’s overkill for most users.

For most cases, DATEDIF is the simplest and most reliable method.

Frequently Asked Questions

Why does DATEDIF not show up in Excel’s function list?

DATEDIF is a legacy function from Lotus 1-2-3 that Microsoft kept for compatibility but hid from the Insert Function dialog. It still works perfectly; you just need to type it manually. It’s documented in older Excel versions but not in newer help files, yet it remains fully supported.

How do I calculate age in years only?

Use =DATEDIF(start, end, "Y") to get the number of complete years. For example, from 1990-05-15 to 2025-01-01, it returns 34. If you need a decimal, use =(end-start)/365.25.

What if the end date is today?

Replace the end date cell with TODAY(). For example, =DATEDIF(A1, TODAY(), "Y") gives the current age in years. The TODAY function updates automatically each day, so the age stays current.

Can I calculate age in months and days only?

Yes. For total months, use =DATEDIF(start, end, "M"). For months ignoring years, use “YM”. For days ignoring months, use “MD”. To get total days, simply subtract the dates: =end - start (formatted as a number).

Why does my DATEDIF formula return #NUM!?

This error occurs when the start date is later than the end date. Check that your start date is earlier. Also, ensure both cells contain valid dates, not text. If you’re using a date format that Excel doesn’t recognize, use the DATE() function.

Does DATEDIF account for leap years correctly?

Yes, because it works with actual calendar dates. For example, from 2020-02-29 to 2021-02-28, DATEDIF with “Y” returns 0, because a full year hasn’t passed—February 29 in a leap year is considered the end of the year. This matches how most people calculate age.

Related

• Best Games Like Marathon: Why Hunt Showdown 1896 Leads
• Apple Vision vs Phone Camera for Pickleball Tracking
• How to Find and Install Games Removed From Steam

in start | Start date (YYYY-MM-DD) | - | 1990-05-15 | 0
in end | End date (YYYY-MM-DD) | - | 2025-01-01 | 0
out years | Years | yr | DATEDIF(start,end,"Y") | 0
out months | Months | mo | DATEDIF(start,end,"YM") | 0
out days | Days | d | DATEDIF(start,end,"MD") | 0