Excel Formula to Calculate Age From a Birthday

Formulas & functions · Updated November 19, 2026

In Excel for Microsoft 365, use =DATEDIF(A2,TODAY(),"Y") to calculate someone’s age in completed years, assuming the birthday is a real Excel date in cell A2. The result updates as the current date changes; for an age on a chosen date, replace TODAY() with a cell containing that date.

The simplest age formula

DATEDIF calculates the difference between two dates. Its final argument, "Y", tells Excel to return the number of complete years between them. This is usually what people mean by age: the person’s age in years after their most recent birthday, not a rounded estimate based on the number of days since birth.

With a birthday in A2 and today set to November 3, 2026, the formula returns 25 for a birthday of November 4, 2000. The person will turn 26 the following day. If A2 contains November 3, 2000, the formula returns 26 because that birthday falls on the calculation date. The result is a number, so you can use it in other calculations, filters, or summaries.

Enter the formula in the row where you want the age, such as B2, and press Enter. To calculate ages for more rows, drag the fill handle at the lower-right corner of B2 down the column, or copy and paste the formula. Excel adjusts A2 to A3, A4, and so on as it fills the formula down.

How DATEDIF works

DATEDIF takes a start date, an end date, and a unit describing what to count. For an age, the birthday is the start date and the current or chosen date is the end date. The "Y" unit counts whole years, leaving out any unfinished portion of the current year.

DATEDIF is an older Excel function that remains available, though it may not appear in the function autocomplete list or in the usual function help in some Excel builds. Type it directly into a formula and include the opening and closing parentheses. The unit must be in quotation marks. Common units include "Y" for complete years, "M" for complete months, and "D" for days. Units such as "YM" return remaining months after complete years, but they are not needed for a simple age-in-years result.

Do not substitute "M" and divide by 12 if you need age in whole years. A person who is 25 years and 11 months old has completed 25 years, even though the total number of complete months divided by 12 may require extra rounding decisions. The years unit directly answers the completed-age question.

Use a chosen date instead of today

A fixed calculation date is useful for reports, historical records, eligibility snapshots, or worksheets that need to reproduce the same result later. Put the date to calculate against in B1. With birthdays in A2:A20 and the calculation date in B1, enter this in the age column beside the first birthday:

=DATEDIF(A2,$B$1,"Y")

The dollar signs make B1 an absolute reference, so it stays fixed when the formula is filled down. For example, if B1 contains November 3, 2026 and A2 contains November 4, 2000, the result is 25. If the birthday is November 3, 2000, the result is 26. Make sure B1 contains a real date, not text that merely looks like one.

If the worksheet should always calculate age as of today, use TODAY() rather than storing a date manually. TODAY returns the current date according to Excel’s calculation environment and has no time component. It is a volatile function, meaning Excel can recalculate it when the workbook recalculates. As a result, an age based on TODAY may change after a birthday without anyone editing the birthday cell. For a dated report that must remain historically consistent, use a fixed date instead.

Check the birthday data before calculating

The formula works reliably when the birthday is stored as an Excel date value. A date can look correct on screen but still be text, especially if it was imported from a website or CSV file, copied from another system, or entered in a format Excel does not recognize under the computer’s regional settings. A text date may cause an error or be interpreted inconsistently.

  • Enter dates in a format Excel recognizes, or construct them with Excel’s date controls. For unambiguous worksheet formulas, the DATE function can create a date from year, month, and day numbers.
  • Change the cell’s display using Home > Number format or press Ctrl+1 on Windows to open Format Cells. Formatting changes how a date appears; it does not, by itself, convert text into a genuine date.
  • If an imported birthday is left-aligned or does not behave like other dates, check whether it is text. Convert or re-import it as a date before relying on age calculations.
  • Check the year, month, and day separately when a result seems wrong. A value entered as 04/05/2000 can mean April 5 or May 4 depending on the date convention used when it was entered.

Excel stores dates as serial values, which lets it compare and calculate them. The number format controls whether the value appears as, for example, 11/3/2026 or November 3, 2026. A display format that shows only the year does not remove the month and day used by DATEDIF.

Handle blank and future birthdays

A blank birthday cell can be treated like a zero date in some calculations, producing a misleadingly large age. If empty rows are possible, use a check that returns a blank until a birthday is entered. With birthdays in A2 and the desired result in B2, use:

=IF(A2="","",DATEDIF(A2,TODAY(),"Y"))

This leaves B2 empty when A2 is empty and otherwise calculates age. A birthday later than today is a different issue: it is likely a data-entry mistake, and DATEDIF cannot calculate a negative age in this setup. A guarded formula can display a message instead. With the birthday in A2, use:

=IF(A2="","",IF(A2>TODAY(),"Check date",DATEDIF(A2,TODAY(),"Y")))

The formula leaves the result blank for an empty birthday, displays “Check date” for a future date, and calculates age otherwise. Because the result column can now contain either a number or text, do not assume every cell in it is numeric when building later totals or charts. You can also use Excel’s data validation features to reduce accidental entries, but validate the actual imported data as well.

Leap-day birthdays and what age means

Someone born on February 29 has a birthday date that occurs only in leap years. DATEDIF still returns completed years, but the date on which a person is considered to have aged a year in a non-leap year depends on the convention being used. Some contexts treat the birthday as February 28; others use March 1. Excel’s formula cannot determine a legal, organizational, or personal convention for you.

If the exact treatment matters, agree on the convention first and verify the formula against dates around February 28 and March 1 in both leap and non-leap years. This is especially important for official forms, age-based rules, or records where a one-day difference could affect an outcome. Use the applicable policy or ask a qualified professional where the question is legal or regulatory; a spreadsheet formula only applies the date logic it was given.

For ordinary birthday tracking, DATEDIF is a practical choice because it counts completed years without rounding a fractional year. Avoid using a simple subtraction of birth year from current year: that overstates age before the person’s birthday in the current year. For example, subtracting 2000 from 2026 gives 26 even when the November 4 birthday has not arrived by November 3.

Choosing between DATEDIF and other methods

For most worksheets, DATEDIF is the clearest option when the desired answer is a person’s age in completed years. It handles the comparison of the month and day as well as the year, which is why it is more dependable than subtracting year numbers. Use a fixed end-date cell when results need to be reproducible, and TODAY when the age should move forward automatically.

YEARFRAC is another Excel function that expresses the fraction of a year between dates. It can be useful for elapsed-time calculations, but its result depends on the selected day-count basis, and rounding a fraction to an integer can make an age formula less transparent. It is not a direct replacement when the requirement is specifically “completed birthdays.” Similarly, subtracting dates and dividing by 365 or 365.25 is an approximation: years do not all have the same number of days, and that approach can misstate age near a birthday.

For a workbook that needs years, months, and days rather than just whole years, build and test that output separately. DATEDIF can return several date-difference units, but combining them requires care, particularly for dates at the end of a month and February 29. First decide whether the goal is a conventional age, elapsed time, or a display such as “25 years, 11 months.” They are related but not identical reporting needs.

Common mistakes and quick checks

  • Using the wrong end date: TODAY is appropriate for current age, but not for a past report that needs to remain unchanged. Use a date cell for a historical calculation.
  • Leaving out quotation marks: The unit must be written as "Y", including the quotes. Check that the formula has three arguments separated by commas in a standard English-language Excel formula.
  • Reversing the dates: Put the birthday first and the later calculation date second. If the start date is after the end date, DATEDIF can return a number error.
  • Assuming a formatted value is a date: Changing a text cell’s number format does not necessarily convert it. Verify that Excel recognizes the birthday as a date value.
  • Rounding a decimal-year result: Rounding can make someone appear to reach the next age before their birthday. Use the complete-years unit instead.

If the displayed age does not match expectations, test one row by writing down the full birthday and the calculation date, then compare their month and day. Check whether the birthday has occurred yet in the calculation year. Also confirm that the workbook is recalculating and that the date cell has not been entered as text. These checks usually identify whether the problem is the formula, the source data, or the chosen age convention.

Frequently Asked Questions

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

How do I calculate age as of a specific date?
Put the date to calculate against in B1. With the birthday in A2, use =DATEDIF(A2,$B$1,"Y"). The absolute reference keeps B1 fixed when you copy the formula down.

Why does my age formula return an error?
Check that both dates are real Excel dates and that the birthday is not later than the end date. Also confirm that the formula uses the three DATEDIF arguments in the right order and includes quotation marks around Y.

Can I calculate age in months instead of years?
Yes. With the birthday in A2 and today as the end date, =DATEDIF(A2,TODAY(),"M") returns complete months. This is a total month count, not a years-and-extra-months display.

Will the age update automatically?
Yes, when the formula uses TODAY(), Excel can recalculate the result as the current date changes. A formula that uses a fixed date in a cell will continue to calculate as of that date until you change the cell.

Related

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.