Age Calculator Excel Formula: How to Use It

In Excel, the core formula for calculating age is =DATEDIF(birthdate, TODAY(), "Y"), which returns the number of complete years between a person’s birth date and the current date. You enter a birth date in one cell, and this formula gives you the age in years, with the option to also calculate months and days. This article explains the formula, how to use it, common pitfalls, and practical variations.

Formula

In Excel, the DATEDIF function calculates the difference between two dates in years, months, or days. The syntax is DATEDIF(start_date, end_date, unit). For age, start_date is the birth date, end_date is the current date (or a specific reference date), and unit is a text code that determines the output:

• "Y" – complete years
• "M" – complete months
• "D" – days
• "YM" – months remaining after the last full year
• "YD" – days remaining after the last full year (ignores months)
• "MD" – days remaining after the last full month

For a full age breakdown, you can combine units: =DATEDIF(birth, TODAY(), "Y") gives years, =DATEDIF(birth, TODAY(), "YM") gives the extra months, and =DATEDIF(birth, TODAY(), "MD") gives the extra days. The formula uses the birth date as the start and the current date as the end, so it automatically updates each day.

Worked Example

Using the default values from the calculator: birth date = 1990-05-15, today = 2025-01-01.

1. Calculate complete years: =DATEDIF("1990-05-15", "2025-01-01", "Y") → 34 (because the 34th birthday was May 15, 2024, and the next birthday hasn’t occurred yet).
2. Calculate remaining months: =DATEDIF("1990-05-15", "2025-01-01", "YM") → 7 (from May 15 to December 15 is 7 months, then the next month boundary is January 15, which hasn’t been reached).
3. Calculate remaining days: =DATEDIF("1990-05-15", "2025-01-01", "MD") → 17 (from December 15 to January 1 is 17 days).

So the person is 34 years, 7 months, and 17 days old on January 1, 2025. This matches the calculator output.

Why Use DATEDIF Instead of Simple Subtraction?

Simply subtracting the birth date from today (=TODAY()-birthdate) returns the total number of days, which is not intuitive for age. Dividing by 365.25 gives a decimal number like 34.56, which is hard to interpret and can be inaccurate due to leap years. DATEDIF gives whole years, months, and days, which is exactly how we state age in everyday language. It also handles leap years correctly because it counts calendar boundaries, not fixed day lengths.

How to Use the Formula in Excel

To calculate age in Excel, follow these steps:

1. Enter the birth date in a cell, e.g., A1 as 05/15/1990.
2. In another cell, type =DATEDIF(A1, TODAY(), "Y") and press Enter.
3. The cell shows the age in years. For months and days, use =DATEDIF(A1, TODAY(), "YM") and =DATEDIF(A1, TODAY(), "MD").
4. To combine into a single text string, use: =DATEDIF(A1, TODAY(), "Y") & " years, " & DATEDIF(A1, TODAY(), "YM") & " months, " & DATEDIF(A1, TODAY(), "MD") & " days".

If you need the age as of a specific date (not today), replace TODAY() with that date cell, e.g., =DATEDIF(A1, B1, "Y") where B1 contains the reference date. This is useful for historical records or future planning.

Common Mistakes and How to Avoid Them

One frequent error is using YEARFRAC or simple division, which yields decimals. Another is mixing up the start and end dates in DATEDIF – the start date must be earlier than the end date, otherwise the function returns an error. Also, ensure the cells are formatted as dates, not text; text dates can cause #VALUE! errors. When using DATEDIF with the "MD" unit, be aware that it can sometimes return negative or unexpected results if the end date is in a month with fewer days than the start date’s month (e.g., from January 31 to February 28). In such cases, Excel adjusts, but it’s worth testing. Finally, DATEDIF is not documented in Excel’s function list for some versions, but it works in all modern versions; you must type it manually.

Alternatives to DATEDIF

If you prefer a formula that doesn’t rely on a hidden function, you can use =INT((TODAY()-A1)/365.25) for years, but this is less accurate because it assumes a fixed year length. Another option is =YEARFRAC(A1, TODAY()) which returns a decimal year, but you’d need to round down with =INT(YEARFRAC(A1, TODAY())). For a more precise age in years and months, you can combine YEAR, MONTH, and DAY functions, but that becomes complex. DATEDIF remains the simplest and most reliable for age calculations.

Frequently Asked Questions

Does DATEDIF work in all Excel versions?

Yes, DATEDIF works in Excel 2007, 2010, 2013, 2016, 2019, 2021, and Microsoft 365, as well as Google Sheets. However, it is not listed in the function wizard, so you must type it manually. It is a legacy function from Lotus 1-2-3, but Microsoft kept it for compatibility.

How do I calculate age in years and months only?

Use =DATEDIF(A1, TODAY(), "Y") for years and =DATEDIF(A1, TODAY(), "YM") for months. Combine them with an ampersand: =DATEDIF(A1, TODAY(), "Y") & " years, " & DATEDIF(A1, TODAY(), "YM") & " months".

Why does my DATEDIF return a #NUM! error?

This error occurs when the start date is later than the end date. Check that the birth date is in the past relative to the reference date. Also, ensure both cells contain valid dates, not text strings.

Can I calculate age from a list of birthdates at once?

Yes, enter the formula in one column and drag the fill handle down. Each row will use its own birth date cell. For example, if birth dates are in A2:A10, put =DATEDIF(A2, TODAY(), "Y") in B2 and copy down.

How do I calculate age as of a specific date in the past?

Replace TODAY() with a cell containing that date. For instance, =DATEDIF(A1, B1, "Y") where B1 is the cutoff date. This is useful for eligibility checks or historical records.

Is there a way to calculate age in decimal years?

Yes, use =YEARFRAC(A1, TODAY()) which returns a decimal like 34.62. However, this is not the same as completed years; it’s a fractional representation. For most age contexts, whole years with months and days are preferred.

Related

• Flight Time Calculator With Speed: How It Works
• Ford V Series Code Calculator: Decode Your Engine
• Free Clock In Calculator: Track Time Easily

in birth | Birth date | date | 1990-05-15 | 0
in today | Today's date | date | 2025-01-01 | 0
out age_years | Age in years | years | DATEDIF(birth, today, "Y") | 0
out age_months | Remaining months | months | DATEDIF(birth, today, "YM") | 0
out age_days | Remaining days | days | DATEDIF(birth, today, "MD") | 0