Calculate Age in Excel With a Birthday Date Formula

To calculate someone’s age in completed years in Excel, enter their birthday as a real date in A2, then use =DATEDIF(A2,TODAY(),"Y"). The result updates with the current date, so it shows the person’s age today rather than the age they were when you first entered the birthday.

How the age formula works

With a birthday in A2, DATEDIF compares that date with today and returns the number of complete years between them. The final argument, "Y", tells Excel to return whole years, not a decimal or a count of calendar-year boundaries. For example, if A2 contains May 20, 2000, and today is May 19, 2026, the formula returns 25. On May 20, 2026, it returns 26.

The birthday must already have occurred in the current year for the age to increase. That is why subtracting the birth year from the current year is not enough: that shortcut can report someone as a year older before their birthday arrives.

DATEDIF is a legacy Excel function that remains available in current desktop Excel, including Microsoft 365, although it may not appear in the formula autocomplete list. Type it directly, including the quotation marks around the unit. The formula uses commas as argument separators, as required here; Excel installations configured for some other regional settings may expect semicolons instead.

Enter birthdays as real Excel dates

Excel calculates with date values, not merely with text that looks like a date. If a cell contains text such as “May 20, 2000,” Excel may not interpret it as a date, particularly when the workbook’s regional date settings differ from the format used to enter it. A recognizable date is usually right-aligned by default, but alignment alone is not a definitive test.

To set up a simple list, put a heading such as Birthday in A1, enter each person’s date in A2:A20, and put Age in B1. Enter the formula in B2, then copy it down the column. Excel adjusts the relative reference from A2 to A3, A4, and so on. To change how a date displays, select the date cells and use Home > Number Format; changing the display format does not change the date value used in the calculation.

For reliable data entry, use a consistent format, such as 20-May-2000, and make sure Excel recognizes it as a date. If a list is imported from a text file, check a few entries before filling formulas down: ambiguous dates such as 03/04/2000 can mean March 4 or April 3 depending on regional settings. A wrong interpretation can produce a plausible-looking but incorrect age.

Calculate age on a chosen date

Sometimes the question is not how old someone is today, but how old they were on a particular date—for example, on the date of an event, application, or annual report. Put the birthday in A2 and the date to calculate against in B2. Use =DATEDIF(A2,B2,"Y") in C2. If A2 contains May 20, 2000, and B2 contains May 19, 2026, C2 returns 25; if B2 is May 20, 2026, it returns 26.

This version is useful for a repeatable report because the result depends on the two dates in the sheet, not on the day someone opens the workbook. You can enter the reference date once in a cell and use that same cell in each row—for example, keep the report date in B1 and put birthdays in A2:A20. In that layout, use =DATEDIF(A2,$B$1,"Y") in C2 and copy it down. The dollar signs keep the reference date fixed as the formula is copied.

Both dates need to be valid Excel dates, and the birthday must not be later than the reference date. If the start date is later, DATEDIF returns an error rather than a negative age. That behavior is useful as a warning, but a future-date check can make the output clearer.

Show years and months instead of years alone

For some lists, a whole-number age is too broad. To show complete years plus the additional complete months, put the birthday in A2 and use =DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months". The first calculation returns complete years; the "YM" unit returns the months remaining after those years are counted. For a birthday 25 years and 3 complete months before today, the displayed result is “25 years, 3 months.”

The ampersands join the numeric results to text labels, so the output is text rather than a number. That is convenient for display, but less suitable if you need to sort or calculate with the age as a numeric value. Keep the years-only result in its own column when later formulas need to compare ages, filter records, or calculate statistics.

Be cautious about adding a days component with DATEDIF’s "MD" unit. Excel’s documentation warns that this unit can return inaccurate results in some situations. If exact years, months, and days are needed for a business rule, define how month lengths and leap-day birthdays should be handled, then use a method designed for that rule rather than assuming every month has the same number of days.

Handle blank cells and future birthdays

If you copy the basic formula into rows where some birthdays have not been entered, blank cells can produce confusing results. With birthdays in A2:A20, use =IF(A2="","",DATEDIF(A2,TODAY(),"Y")) in B2 and fill it down. The formula leaves the result blank when A2 is empty and otherwise calculates age in completed years.

A date accidentally entered in the future causes an error in the basic calculation. To show a readable warning instead, with the birthday in A2, use =IF(A2="","",IF(A2>TODAY(),"Check birthday",DATEDIF(A2,TODAY(),"Y"))) in B2. It leaves missing birthdays empty, flags dates after today, and calculates an age for other dates. Because the output can be either text or a number, this version is best for a display column rather than a column used for numeric analysis.

Also check that a cell is not displaying a date when you expected an age. If the result cell has a date number format, Excel may show the formula’s numeric result as a calendar date. Select the result cells and choose General or Number from Home > Number Format. A whole-number age should display as a number such as 25, not as a date.

Understand TODAY and automatic updates

TODAY() returns the current date, without a time. A formula that uses it can therefore show a different age after the birthday passes, even though no one edits the birthday cell. Excel recalculates formulas when the workbook recalculates; if results do not appear to update, check that calculation is set to Automatic under Formulas > Calculation Options.

This automatic behavior is generally right for a live age list. It is not ideal when a report must preserve the age as it was on a past publication date. For that, use a date stored in a cell as the reference date, as described above, and retain that date with the report. A formula tied to TODAY() is dynamic; a formula tied to a fixed reference date is reproducible.

Excel for the web also supports ordinary date formulas such as DATEDIF and TODAY. The same formula approach applies there. On Mac, the formula is the same as on Windows; the ribbon layout can differ slightly, but no special Mac-only age function is required.

Leap-day birthdays and age conventions

A person born on February 29 has a birthday date that does not appear in most years. The formula still calculates complete years, but a spreadsheet cannot decide which non-leap-day convention should apply if a legal, policy, or organizational rule treats the birthday as February 28 or March 1. Rules can depend on the jurisdiction or purpose, so do not assume one convention is universal.

If the result is being used for eligibility, compliance, or another decision with consequences, confirm the applicable rule and test the formula around the relevant date. For a simple personal age list, the standard DATEDIF calculation is often adequate; for a formal rule, document the convention and build the calculation around it. A result can be mathematically consistent with a date comparison while still differing from a particular organization’s definition of when age changes.

Why YEARFRAC or simple subtraction may not fit

A common shortcut is to subtract the birth year from the current year. With a birthday in A2, =YEAR(TODAY())-YEAR(A2) counts the difference between calendar years, but it does not check whether this year’s birthday has happened. A person whose birthday is still ahead may therefore appear one year too old. Use it only when that limitation is acceptable, not as a precise age calculation.

YEARFRAC is useful when you want an elapsed-year fraction, such as 25.4 years, but a fraction is not the same as age in completed years. Its result can depend on the selected day-count basis, which specifies how a year is measured for the fraction. For a conventional whole-number age, DATEDIF with "Y" expresses the intention more directly.

Likewise, a formatted difference between dates may look convenient but can wrap or display an unsuitable number of days rather than a person’s completed age. Choose the calculation based on the question: completed years for ordinary age, years and months for a more detailed display, or a fractional year for elapsed-time analysis.

Frequently Asked Questions

What is the simplest Excel formula to calculate age?
With the birthday in A2, use =DATEDIF(A2,TODAY(),"Y"). It returns the number of complete years as of today and updates when the current date changes.

Why does my age formula return #NUM!?
The birthday may be later than the date being used as the calculation endpoint, or the cell may contain a date Excel cannot interpret correctly. Check that the birthday is a valid date and is not in the future; for a past-date calculation, also check that the reference date is on or after the birthday.

Can I calculate age on a past date?
Yes. Put the birthday in A2 and the reference date in B2, then use =DATEDIF(A2,B2,"Y"). This calculates completed years on the chosen reference date rather than today.

How do I calculate age in years and months?
With the birthday in A2, combine DATEDIF(A2,TODAY(),"Y") and DATEDIF(A2,TODAY(),"YM") with text labels. The years unit gives completed years, while the months unit gives additional complete months after those years.

Does the age formula update automatically on a birthday?
Yes, if it uses TODAY() and Excel recalculates the workbook. If you need a result that stays tied to a particular reporting date, use a date entered in a cell instead of TODAY().

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.