Calculate Age in Excel by Birth Date: Exact Formulas

Formulas & functions · Updated November 20, 2026

To calculate age in completed years, put the birth date 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 current date changes. You will need birth dates stored as real Excel dates, rather than text that only looks like a date.

What You’ll Need

  • A worksheet with one birth date per person, such as dates in column A starting at A2.
  • An empty column for the age results.
  • Microsoft Excel for Microsoft 365 on Windows, or Excel for Mac or the web. DATEDIF and TODAY work in each of these; the formula is the same.

If your Excel uses semicolons as formula separators because of your regional settings, replace the commas in the examples with semicolons. The examples below use comma separators, as requested for standard Excel syntax.

Step-by-Step

  1. Arrange the birth dates in a column. Put a clear heading such as Birth date in A1, then enter each person’s date in A2, A3, and so on. Keep one date in each cell. Avoid adding a person’s name or notes in the same cell as the date, because Excel needs to interpret the cell value as a date for the calculation to work.
  2. Check that Excel recognizes the dates. Select a birth-date cell and look at the formula bar. A valid date generally appears as a date value, and changing the cell’s number format should change how it is displayed. If you are unsure, select the column and choose Home > Number Format > Short Date. In Excel for Mac, use the Home tab’s Number Format control; in Excel for the web, use the Number Format dropdown on Home. A display change is a useful check, though text values may need converting as described under Troubleshooting.
  3. Choose a result column. Enter a heading such as Age in B1. Select B2, beside the first birth date. Keeping the results next to the source dates makes it easier to check each person’s age and copy the calculation down the list.
  4. Enter the age formula. With the birth date in A2 and the result in B2, enter =DATEDIF(A2,TODAY(),"Y"). Press Enter. DATEDIF compares the starting date with the ending date, and the unit “Y” asks for completed years. For example, if A2 contains 15-Aug-1990 and today is 3-Nov-2026, the result is 36: the person’s 36th birthday has passed in 2026.
  5. Copy the formula for the remaining rows. Select B2 and drag the small square at the cell’s lower-right corner down the column. You can also copy B2, select the destination cells, and paste. Excel changes A2 to A3, A4, and so on automatically, while TODAY continues to refer to the current date. If your data is formatted as an Excel table, entering the formula in the first row of the Age column usually fills the column automatically.
  6. Use a fixed date when you need an age at a past or future point. For a report that must be reproducible, place the date to calculate against in B1, and put the birth date in A2. In C2, enter =DATEDIF(A2,B$1,"Y"). The dollar sign keeps the reference on row 1 when you copy the formula down. For example, with a birth date of 15-Jun-2000 in A2 and 1-Jun-2026 in B1, the formula returns 25 because that year’s birthday has not yet occurred. You can type a date directly into B1 or use a date from a report’s cutoff period.
  7. Optionally suppress results for empty rows and flag future dates. If the list has blank rows, you may prefer a formula that leaves the result blank until a birth date is entered and displays a message for dates later than today. With birth dates in A2 downward and the result in B2, use =IF(A2="","",IF(A2>TODAY(),"Check birth date",DATEDIF(A2,TODAY(),"Y"))). Copy it down as before. The result is a number for a valid past birth date, blank when A2 is empty, and a warning rather than a #NUM! error for a future date. This formula is useful when other people will enter dates into the sheet.
  8. Format and review the results. Age in completed years is a whole number, so select the result column and choose Home > Number Format > Number, or leave Excel’s General format if it displays as expected. Check a few rows manually, especially dates close to a birthday, and verify that each result sits beside the correct birth date. Save the workbook after checking the output.

DATEDIF is an older Excel function that remains available in Microsoft 365, Excel for Mac, and Excel for the web, although Excel may not show it in formula autocomplete or provide the same prompting as newer functions. Type the function and its arguments exactly as shown. The “Y” unit returns whole years, not a decimal fraction and not a rounded age.

Common Mistakes

  • Using YEAR subtraction alone. Subtracting the birth year from the current year can overstate someone’s age by one if their birthday has not happened yet this year. DATEDIF with “Y” accounts for the month and day as well as the year.
  • Entering a date as text. A value such as an imported date may look like 04/05/2000 but be stored as text, or it may be interpreted as April 5 in one regional setting and May 4 in another. Confirm the intended month and day before relying on the ages.
  • Using the wrong DATEDIF unit. The unit must be in quotation marks. “Y” gives completed years; it is not interchangeable with “M” or “D”, which count months or days. A misspelled unit or unmatched quotation mark can produce an error.
  • Expecting a decimal age. The “Y” result counts full birthdays only. A person who is 20 years and 11 months old still returns 20. If you need an age for a specific policy or reporting convention, confirm how that convention defines age rather than rounding the result.
  • Overwriting the birth-date column. Put the formula in a separate column. Replacing the original dates makes later checking and correction difficult, and the formula cannot calculate an age if its source date has been removed.
  • Assuming every blank is harmless. A blank source cell can be treated like a zero date in an unguarded calculation, producing a very large age or an error. Use the optional IF formula above when the list may contain empty cells.

Tips

  • Use a fixed as-of date for official snapshots. TODAY recalculates, so an age list made today can show different values later. A date entered once in a clearly labeled cell, such as Report date, lets you reproduce the same age results next month. It also makes the report’s time basis visible to anyone reading it.
  • Keep the source data intact. Store birth date, name, and calculated age in separate columns. Consider converting the range to a table with Insert > Table. Tables make headings clearer and commonly extend formulas when new records are added.
  • Use an unambiguous date entry method. Dates such as 03/04/2001 can be read differently depending on regional settings. Use a consistent date format for the workbook and check imported data. A four-digit year helps avoid confusion between years such as 1926 and 2026.
  • Know when the result updates. TODAY changes when Excel recalculates the workbook, usually when it opens or when a calculation-triggering edit occurs. If results seem stale, choose Formulas > Calculation Options > Automatic in Excel for Windows. Excel for Mac has calculation options on the Formulas tab as well; Excel for the web recalculates supported formulas automatically.
  • Define a convention for February 29 birthdays. A leap-day birthday does not occur in most years, and organizations may use different rules for when the age is considered to advance in a non-leap year. If the result is for a legal, eligibility, or administrative decision, confirm the applicable rule and test the chosen convention rather than assuming every context uses the same one.

Troubleshooting

The formula returns #NUM!. DATEDIF cannot calculate a completed age when its start date is later than its end date. Check for a birth date entered in the future, a date/month reversal, or an incorrect report date. If people will enter dates themselves, use the optional formula above to flag future dates for review.

The formula returns #VALUE! or does not calculate. The birth date may be text, or the formula may contain a typo. Re-enter the formula with straight quotation marks around “Y” and balanced parentheses. To convert an imported column, select it and try Data > Text to Columns, then follow the prompts and choose a date order that matches the source data. Do not choose a month/day order by guesswork: verify the source format first. If the date is text in a consistent pattern, converting it to a real date before using DATEDIF is safer than calculating against ambiguous text.

The result looks like a date instead of an age. The result cell may have inherited a date format. Select the age cells and change Home > Number Format to General or Number. The underlying formula may be correct even when the display format is not.

The age is one year higher or lower than expected. Check the birthday against the date used as the endpoint. A birthday later in the year has not yet been reached, so the age is one less than the simple difference between the two calendar years. Also make sure the formula references the intended birth-date row and, for a fixed-date calculation, the correct report-date cell.

DATEDIF is not suggested as you type. That does not necessarily mean the function is unavailable. Type the complete formula manually, including the unit in quotation marks, then press Enter. If Excel shows a formula error immediately, check your regional argument separator settings; some installations require semicolons instead of commas.

Frequently Asked Questions

Does DATEDIF update each person’s age automatically?
Yes, when the formula uses TODAY as its ending date. Excel recalculates the result based on the current date. For a saved snapshot that should stay the same, use a fixed date in a cell instead.

Can I calculate someone’s age on a particular date?
Yes. Put the birth date in A2 and the date to calculate against in B1, then use =DATEDIF(A2,B$1,"Y") in C2. The formula returns completed years as of B1. Check that the date in B1 is a real Excel date and is not earlier than the birth date.

Why does Excel not show DATEDIF in the function list?
DATEDIF is supported in Excel but may not appear in autocomplete or the usual function prompts. Type it manually in the formula bar or cell. Its syntax is DATEDIF(start_date,end_date,unit), with “Y” in quotes for completed years.

Will this work in Excel for Mac or Excel for the web?
Yes. DATEDIF and TODAY are available there, and the formula itself is the same. Ribbon controls and their locations can differ slightly by platform, but formula entry and copying the result down a column work in each version.

Can I show years and months instead of only whole years?
Yes, DATEDIF supports additional units for that purpose, but make sure the display matches what readers need and test it around birthdays. For a basic age field, whole completed years are usually clearer. If you report months as well, label the result explicitly so it is not mistaken for a decimal age.

Is an age calculated this way suitable for legal or eligibility decisions?
The formula provides completed years according to the dates entered and Excel’s date handling. A law, policy, or eligibility program may specify a different date cutoff or a special rule, including for leap-day birthdays. Confirm the applicable definition before using a spreadsheet result to make a decision.

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.