Calculate Age in Excel From a Birth Date, Step by Step

To calculate someone’s age in completed years in Excel, put their birth date in A2 and enter =DATEDIF(A2,TODAY(),"Y") in another cell. For example, if A2 contains January 15, 2000, the result is 26 on November 2, 2026. The formula updates as the current date changes, so first make sure Excel recognizes the birth date as a real date rather than text.

What You’ll Need

A Microsoft Excel worksheet, using Excel for Microsoft 365 on Windows for these steps. The formula also works in current Excel for Mac and Excel for the web.
A column containing valid birth dates, with one date per person. This guide assumes the first date is in A2.
An empty column or cell where you can show the calculated age.

Step-by-Step

Set up the birth-date column. Add a heading such as Birth Date in A1, then enter or paste each person’s date in A2, A3, and the rows below. Use a date format that is clear to you, such as 15-Jan-2000, especially if your data includes dates like 04/05/2000 that could mean different things in different regions. Make sure each entry includes a year and represents the intended date.
Check that Excel recognizes the dates. Select a birth-date cell and look at the Number Format box on the Home tab. If it shows a date format, Excel likely recognizes the value as a date. You can also change the display using Home > Number Format > Short Date. Changing the display format does not repair text that only looks like a date; if the value remains left-aligned or does not respond to date formatting, see Troubleshooting below.
Choose the age-result column. Enter Age in B1, leaving B2 available for the first result. Keeping birth dates and ages in separate columns makes the worksheet easier to review, sort, and filter. If your dates are in another column, adjust the cell reference in the formula accordingly.
Enter the age formula. With the birth date in A2 and the result in B2, type =DATEDIF(A2,TODAY(),"Y") in B2 and press Enter. DATEDIF returns the number of complete years between the two dates. In the worked example above, a birth date of January 15, 2000 produces 26 on November 2, 2026. TODAY() supplies the current date from your computer’s date settings, so you do not have to update the formula each day.
Copy the formula down the list. Select B2 and drag the small square at the lower-right corner of the selection (the fill handle) down alongside the remaining birth dates. Excel adjusts A2 to A3, A4, and so on. For a continuous list, you can also select the formula cell and the empty cells beneath it, then choose Home > Fill > Down. On Windows, Ctrl+D fills down; on Mac, Control+D is the fill-down shortcut in Excel. Check the last few rows to confirm that each age refers to the birth date on the same row.
Make the result easy to read. The formula returns a number, so General or Number format is suitable. If you see a date such as January 1900 instead of an age, select the result cells and choose Home > Number Format > General or Number. You can add a label in the header, such as Age (years), to make clear that the result counts completed years rather than years rounded to the nearest birthday.
Optionally guard against blank or future dates. If the column may contain empty cells or dates that have not happened yet, use a formula that leaves blanks empty and displays a warning for future dates. With birth dates in A2:A20 and the first result in B2, enter =IF(A2="","",IF(A2>TODAY(),"Check birth date",DATEDIF(A2,TODAY(),"Y"))). For A2 containing January 15, 2000, the result is 26 on November 2, 2026; a blank A2 stays blank, while a future date displays Check birth date. Fill this formula down as in the previous step. Because this formula can return either a number or text, keep the result cells in General format.

DATEDIF is a long-standing Excel function, although it may not appear in the function autocomplete or be described in the usual way in Excel’s function menus. Type it directly as shown. The unit “Y” means complete years; it does not round a person’s age up when their next birthday is approaching.

Common Mistakes

Using YEAR(TODAY()) minus YEAR(birth date). Subtracting just the year numbers can overstate someone’s age by one when their birthday has not occurred yet this year. DATEDIF with the “Y” unit checks whether a full year has elapsed.
Using a fixed date when you want an updating age. Typing today’s date directly into a formula makes the answer stay tied to that date. TODAY() updates with the current date when Excel recalculates. If you need a permanent age as of a past date, use a date stored in a cell instead, as explained in the FAQ.
Entering dates in an ambiguous format. A value such as 03/04/2001 can be interpreted as March 4 or April 3 depending on regional settings. Use an unambiguous entry such as 3-Apr-2001, or confirm the date after import before calculating ages.
Expecting years and months from a years-only result. DATEDIF with “Y” returns a whole number of completed years. It will not show a fractional age, months, or days. Label the column accordingly and choose a different calculation if you need more detail.
Ignoring the birthday rule for February 29. For someone born on a leap day, organizations may use different rules for when the birthday is treated as occurring in a non-leap year. Confirm the rule needed for your purpose, especially for legal, eligibility, or benefits decisions, and verify the result around the relevant date.

Tips

Use a clear table structure. Put headers in the first row and keep each person’s birth date and age on the same row. You can select the data and choose Insert > Table to make a structured list that expands as you add entries. If you use a table, Excel can automatically extend a formula to new rows.
Keep the formula dynamic only when that is useful. TODAY() recalculates, so the age may change on a birthday. That is usually what you want in a current roster. For a report that must preserve the age calculated on a particular day, record the report date and calculate against it rather than relying on a changing current date.
Use a consistent date convention when importing data. For example, agree that dates will be entered as day-month-year and communicate that to everyone adding records. Consistency reduces errors when workbooks are shared across computers with different regional settings.
Protect the original birth dates. Keep the source date column intact and put calculations in a separate column. That makes it possible to audit a surprising age without having to reconstruct or re-enter the source data.
Check the computer date if TODAY() seems wrong. Excel gets the current date from the device’s system clock. If results appear a day ahead or behind, confirm the Windows or Mac date, time, and time-zone settings, then recalculate the workbook.

Troubleshooting

DATEDIF returns #NUM!. The start date is later than the end date in the formula. Check that the birth date is not in the future and that the arguments are in the right order: the birth date comes first, followed by TODAY(). If future dates are possible, use the guarded formula in the steps above.

The result is #VALUE! or an unexpected age. The birth-date cell may contain text, an invalid date, or a date interpreted in the wrong order. Select the cell, set its format to Short Date, and verify the displayed day, month, and year. If the entry is text, re-enter it as a date Excel recognizes or convert the imported column using Data > Text to Columns and the appropriate date order. Make a copy of the data before converting a large imported column.

The answer looks like a date rather than an age. The result cell may be formatted as Date. Select it and choose Home > Number Format > General. The underlying formula may be correct; changing the format changes how the numeric result is displayed.

Every row shows the same age or the wrong person’s age. Confirm that the formula points to the birth date on the same row. In B3, for example, a copied formula should refer to A3, not keep pointing to A2. If you entered dollar signs around the reference, remove them unless you intentionally want a fixed reference.

The age does not update. Press F9 on Windows to recalculate the workbook. On Mac, use Fn+F9 if your keyboard uses function keys for system controls. Also check Formulas > Calculation Options and select Automatic. TODAY() cannot update to the correct date if the computer’s system clock is wrong.

The formula is displayed as text. Change the formula cell’s format from Text to General, then edit the cell and press Enter. Also check that the entry begins with an equals sign and that there is no apostrophe before it. If Excel rejects commas in your regional setup, follow the separator Excel requires in your installation.

Frequently Asked Questions

Does the age formula update automatically?
Yes. TODAY() returns the current date when Excel recalculates, so the age increases when a birthday is reached. If calculation is set to Manual, recalculate with F9 on Windows or the applicable function-key shortcut on Mac, or change Formulas > Calculation Options to Automatic.

Can I calculate age on a date other than today?
Yes. Put the birth date in A2 and the date you want to calculate as of in B2. In C2, enter =DATEDIF(A2,B2,"Y"). If A2 is January 15, 2000 and B2 is January 14, 2026, the result is 25 because the 26th birthday has not yet occurred on the selected date. This is useful for historical reports and fixed eligibility dates.

Can Excel show age in years and months?
Yes, but the basic formula is intended to return completed years only. DATEDIF also supports the “YM” unit for remaining months after complete years, and “MD” for remaining days after complete months. If you combine units in a display formula, check the result around month ends and leap days because calendar conventions can affect how residual days are interpreted.

Why does DATEDIF not appear in Excel’s formula suggestions?
DATEDIF is available in Excel but is not consistently shown in autocomplete or the Insert Function list. Type the function name and arguments directly, making sure the unit is in straight double quotes. A missing quote or mismatched parenthesis can cause a formula error.

Will this work in Excel for Mac or Excel for the web?
Yes. DATEDIF, TODAY, and the formula syntax shown here are supported in current Excel for Mac and Excel for the web. The ribbon layout and keyboard shortcuts can differ slightly by platform; use the equivalent Fill Down or number-format commands if the Windows path is arranged differently in your version.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.