Calculate Age in Excel as of Today: Formulas That Work

To calculate age in completed years as of today, put each person’s date of birth in A2 and enter =DATEDIF(A2,TODAY(),"Y") in another cell. Excel returns the number of full years between the birth date and today, updating the result as the date changes.

The quickest way to calculate age

Use the formula in the first row of your age column, then fill it down for the rest of your list. For example, if A2 contains November 20, 1990, the formula returns 35 on November 2, 2026: that person has not yet reached their birthday for 2026. On November 20, the result becomes 36.

The layout for this basic formula is a date of birth in A2 and the formula in the cell where you want the age. =DATEDIF(A2,TODAY(),"Y")

TODAY() supplies the current date, while the “Y” unit tells DATEDIF to count complete years. This is generally the right result for a birthday-age calculation. It avoids a common mistake: subtracting the birth year from the current year, which can say someone is a year older before their birthday has arrived.

How DATEDIF and TODAY work

DATEDIF measures the interval between a start date and an end date. Its first argument is the birth date, its second is the date to measure through, and its third specifies the kind of interval to return. The function is available in Excel, although it may not appear in the formula autocomplete list. You can type it directly.

"Y" returns complete years.
"M" returns complete months across the entire interval.
"D" returns the total number of days.
"YM" returns remaining months after complete years are removed.
"YD" returns remaining days after complete years are removed.

TODAY() returns the current date without a time. Excel recalculates it when the workbook recalculates, so the age is not a permanently stored snapshot. In Microsoft 365 for Windows, calculation is usually automatic. If the displayed age does not update, check Formulas > Calculation Options > Automatic, or press F9 to recalculate. On a Mac, the Formulas tab also provides calculation options; the exact ribbon layout can vary slightly by Excel release.

Set up birth dates correctly

Excel needs a real date value for the calculation, not text that merely looks like a date. Enter dates in a format Excel recognizes for your regional settings, or construct dates with Excel’s date controls and check the result. A date may display as 11/20/1990, 20-Nov-1990, or another format while still representing the same underlying value.

To check a cell, select it and look at the formula bar and the Number Format control on the Home tab. If a date is left-aligned or formulas return an error, it may have been imported as text. Changing the number format alone does not convert text into a date. Re-enter the value in a recognized date format or use a suitable data-cleaning conversion before calculating ages.

For a list, put a clear heading such as Date of birth in A1 and Age in B1, with records beginning in row 2. After entering the age formula in B2, use the fill handle to copy it down. Keep the birth-date column separate from the calculated age column so the source data remains easy to review.

Show years, months, and days

Sometimes a whole-number age is not detailed enough. To show completed years and the remaining completed months, with dates of birth in A2, use this formula in the result cell: =DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months". For a birth date of November 20, 1990, it displays 35 years, 11 months on November 2, 2026. The ampersands join the calculated values and text labels.

For a years-months-days display, with a valid birth date in A2 and the result in another cell, use =DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months, "&DATEDIF(A2,TODAY(),"MD")&" days". This is convenient for ordinary date ranges, but the “MD” unit has known edge cases around month ends and can produce unexpected results for some date pairs. If exact day-level age matters, test the dates that matter to your use case rather than assuming every calendar convention is handled identically.

For reporting, a numeric age is often more useful than a text string. Text such as “35 years, 11 months” is readable but cannot be sorted or averaged as a number without extra work. Keep the completed-years result in a separate numeric column if you need to filter, group, or summarize records.

Calculate age on a chosen date

For a form, historical report, or age-at-event calculation, use a fixed reference date instead of TODAY(). Put the date of birth in A2 and the chosen as-of date in B2; enter this formula in the result cell: =DATEDIF(A2,B2,"Y"). If A2 is June 15, 2000, and B2 is June 14, 2026, the result is 25. Change B2 to June 15, 2026, and the result is 26.

A fixed date makes the calculation repeatable. By contrast, using TODAY() means the same workbook can show a different age tomorrow, which may be unsuitable when preserving a report or record. For an auditable snapshot, record the as-of date in a cell and retain it with the workbook. Format that cell as a date so readers can see the reference point.

Do not confuse completed age with a decimal estimate of elapsed years. Functions such as YEARFRAC return a fraction based on a day-count convention; they can be useful for time-period analysis, but a result such as 35.95 is not the usual way to state a person’s age. For birthday-based age in whole years, DATEDIF with “Y” is clearer.

Handle blank and future dates

A blank birth-date cell can lead to a misleading number because Excel treats blank references in some date calculations as zero. A future date of birth is invalid for an age calculation and can cause DATEDIF to return an error. If your sheet may contain incomplete or mistaken entries, use a guarded formula instead.

With the date of birth in A2 and the age result in another cell, this formula leaves a blank row empty and flags a date later than today: =IF(A2="","",IF(A2>TODAY(),"Check date",DATEDIF(A2,TODAY(),"Y"))). For a valid birth date of June 15, 2000, it returns 26 on November 2, 2026. If A2 is blank, it returns a blank; if A2 contains a future date, it displays Check date.

This formula intentionally returns either a number or text, so the result column is not purely numeric. That is practical for a small worksheet, but for a report that needs numeric ages, consider a separate validation column to flag missing or future dates and keep the age column numeric. Also decide whether text such as “unknown” should count as a missing date and handle it consistently.

Common mistakes and date edge cases

Subtracting years only: =YEAR(TODAY())-YEAR(A2) can overstate age before the birthday in the current year. It is not a substitute for counting completed years.
Reversing the dates: The birth date must come first and the as-of date second. A start date later than the end date can produce an error.
Using text dates: Imported text may not behave like a true Excel date, even if its appearance is familiar. Convert or re-enter it before relying on the result.
Using age as a duration: A person’s age in years is not simply the number of days divided by 365. Calendar years have different lengths, and birthdays determine when completed age changes.
Ignoring February 29: People born on leap day create a convention question in non-leap years: should the age increment be treated as February 28 or March 1? Excel calculates dates according to its date functions, but a policy, legal, or eligibility rule may define the relevant convention separately. Confirm the rule required for your purpose.

For ordinary personal tracking, these details are usually manageable. For eligibility, compliance, or official records, use the applicable organization’s definition of age and verify unusual dates rather than treating a spreadsheet formula as the authority on policy.

Choosing the right formula for your task

Use =DATEDIF(A2,TODAY(),"Y") when you want the person’s current age in completed years. Choose =DATEDIF(A2,B2,"Y") when the reference date must be fixed or stored in the worksheet. Add a guarded IF when blank or future dates are possible. Use a years-and-months display only when the extra detail helps readers; it is less convenient for calculations than a numeric age.

Excel’s built-in date subtraction can answer a different question: the number of days between two dates. If A2 contains a birth date and B2 an as-of date, the layout for total elapsed days is A2 for the start and B2 for the end; use =B2-A2. This returns a day count, not an age in years. It can help validate that dates are ordered correctly, but it does not replace a birthday-based age formula.

For a large workbook, consider putting the as-of date in one clearly labeled cell instead of repeating TODAY() in many formulas. Then point each age formula to that cell, using an absolute reference if you copy it down. This approach makes the reference date visible and lets you freeze the calculation date for a report by entering a fixed date there.

Frequently Asked Questions

What is the simplest formula to calculate age in Excel?
With a date of birth in A2 and the result in another cell, use =DATEDIF(A2,TODAY(),"Y"). It returns completed years as of today.

Why does DATEDIF not appear in Excel’s function list?
DATEDIF is supported for compatibility but may not be offered in autocomplete or the Insert Function list. Type the formula directly, ensuring the birth date comes first and the end date second.

How do I calculate age on a past date?
Put the birth date in A2 and the historical reference date in B2, then enter =DATEDIF(A2,B2,"Y") in the result cell. The value will remain tied to B2 rather than changing each day.

Can I calculate age in months?
With the date of birth in A2, =DATEDIF(A2,TODAY(),"M") returns complete months across the entire interval. It does not mean the number of leftover months after completed years; use the “YM” unit for that remainder.

Why is the age one year too high?
A formula that subtracts the birth year from the current year ignores whether the birthday has happened yet. Use DATEDIF with the “Y” unit to count only completed years.

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.