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

To calculate someone’s age in completed years, enter their date of birth in B2 and use =DATEDIF(B2,TODAY(),"Y") in another cell. Excel updates the result as the current date changes. This guide shows how to set up the dates, copy the formula, and handle common issues such as text dates and future birthdays.

What You’ll Need

Excel for Microsoft 365 on Windows. The steps also work in recent desktop versions of Excel.
A worksheet with a date-of-birth column. This guide uses B2 for the first date of birth and C2 for the age result; adjust the cell references if your layout differs.
Dates Excel recognizes as dates, rather than text that only looks like a date. You can check this by selecting a date cell and looking at its number format or changing the format temporarily.

Step-by-Step

Set up a column for birth dates. Open the workbook and add a heading such as Date of Birth above the column that will hold the dates. For this example, put the heading in B1 and the first person’s date of birth in B2. If you are starting a new sheet, use a consistent date style, such as 14-Mar-1990, to make day and month order easy to recognize. Excel stores genuine dates as serial numbers, even though it displays them in a familiar date format.
Add a column for age. Put Age in C1. Leave the birth date in B2 and use C2 for the result. Keeping the result separate from the original date makes the sheet easier to read and lets you sort or filter by age later. If you already have a table, use its existing headings and place the formula in the age column beside the first date.
Enter the age formula. With the first date of birth in B2 and the age result in C2, enter =DATEDIF(B2,TODAY(),"Y") in C2, then press Enter. DATEDIF compares the start date with the end date and returns the number of complete years when its final argument is Y. TODAY() supplies the current date, so someone who has not yet had their birthday this year will not be counted as a year older prematurely.
Check the result before filling down. Compare the displayed age with the date of birth and today’s date. For example, if B2 contains 15-Jun-1990 and today is 2-Nov-2026, the result is 36: the person turned 36 on 15-Jun-2026. If the date of birth were 15-Dec-1990, the result on that same day would be 35 because the December birthday has not happened yet. The result should normally appear as a whole number.
Copy the formula to the other rows. Select C2 and point to the small square at the lower-right corner of the selected cell, called the fill handle. Drag it down alongside the remaining dates. Excel adjusts the row reference automatically, so the next row uses B3, the row after that uses B4, and so on. You can also copy C2, select the cells below it, and paste. On Windows, Ctrl+D fills selected cells down when the top cell contains the formula; on Mac, use Command+D. In Excel for the web, dragging the fill handle or copying and pasting are reliable options.
Keep blank rows from showing an age. If the date column includes empty rows, the basic formula may treat a blank as an old date and show a very large age. To return a blank result until B2 contains a date, with the birth date in B2 and the formula in C2, use =IF(B2="","",DATEDIF(B2,TODAY(),"Y")). Enter it in C2 and fill it down as before. This check is useful in a list where new people will be added over time, but it does not replace checking that entered dates are valid.
Format the age column if needed. Select the results, then choose Home > Number Format and select General or Number. If the cells display as dates, currency, or decimals, changing the number format to General usually fixes the display. The age itself is a whole number, so decimal places are not needed. Use the number-format controls on the Home tab in desktop Excel; Excel for the web has similar controls on its Home ribbon.
Choose whether ages should update automatically. TODAY() changes when Excel recalculates, so these ages are current rather than permanent snapshots. If you need ages as of a particular reporting date, put that date in E1 and, with birth dates in B2:B20 and the chosen as-of date in E1, use =DATEDIF(B2,$E$1,"Y") in C2. The dollar signs keep the reference to E1 fixed as you fill the formula down. For example, if B2 is 15-Jun-1990 and E1 is 2-Nov-2026, the result is 36. Change E1 to recalculate every listed age against a different date.

Common Mistakes

Subtracting birth years only. A calculation that subtracts the birth year from the current year ignores whether the birthday has happened yet. It can overstate the age by one for part of each year. DATEDIF with Y counts completed years and accounts for the month and day.

Using the wrong cell reference. If the date of birth is in A2 but the formula points to B2, the result may be wrong or produce an error. Check that the first argument in the formula refers to the date in the same row. When using a fixed as-of date, keep the dollar signs around its cell reference so it does not shift during fill-down.

Entering a date as text. An imported value such as an apostrophe followed by a date, or a date string in a format Excel does not recognize for your regional settings, may not behave like a real date. Confirm the date is recognized by changing the cell’s number format to a date style or checking whether date arithmetic works. Correct the source value if necessary rather than hiding the problem with formatting.

Expecting years, months, and days from one result. The Y argument returns completed whole years only. It does not return a decimal age or a descriptive age such as “36 years, 4 months.” For ordinary age lists, completed years are usually the intended result; if another format is required, be clear about how months and partial years should be counted.

Overlooking leap-day birthdays. People born on February 29 need a consistent rule for non-leap years. DATEDIF calculates completed years based on its date comparison, but official or legal age rules can depend on local requirements. If the result will be used for eligibility, compliance, or a formal decision, confirm the applicable rule rather than assuming Excel’s calculation defines it.

Tips

Use a clear heading such as Age as of today if the sheet uses TODAY(). Use Age as of date if the sheet calculates against a date stored in another cell.
For a formatted Excel table, enter the formula in the first cell of the age column. Excel will often fill the formula down the table column and apply it to new rows, helping keep the list consistent.
Keep the original dates of birth unchanged. If you need to correct a date, fix the source cell and let the age formula recalculate instead of typing over the result.
Remember that TODAY() updates when the workbook recalculates, not necessarily at the instant a file is opened on every device. Excel’s calculation mode is normally Automatic. If results appear stale, check Formulas > Calculation Options > Automatic, then recalculate the workbook. On Windows, F9 recalculates; on Mac, the shortcut can depend on keyboard settings, so use the Formulas ribbon if needed.
If you share a report and need the ages to remain fixed, copy the results and use Paste Special > Values in a separate report copy. This removes the formulas, so keep the original formula-based workbook if you want the ages to update later.

Troubleshooting

The formula returns #NUM!. DATEDIF cannot calculate a completed age if the start date is later than the end date. Check whether the birth date is in the future, whether the intended date was entered in the correct order, and whether your as-of date is earlier than the date of birth. Correct the date rather than changing the formula to conceal an invalid entry.

The result is #VALUE! or an unexpected number. Inspect the birth-date cell. It may contain text, an extra space, or an invalid date such as 31-Apr-2000. Re-enter the date in a format Excel recognizes for your locale, or use Data > Text to Columns to convert consistently formatted imported text dates. Check a few converted values before applying changes to a whole column.

The result looks like a calendar date. The formula may be working, but the result cell has a date number format. Select the age result and choose General or Number from Home > Number Format. If it still looks wrong, check that the formula was entered in the age column and not in the date-of-birth column.

The result is one year higher or lower than expected. Compare the birthday’s month and day with the as-of date, not just the two years. A birthday later in the year has not yet occurred; a birthday earlier in the year has. Also verify that Excel interpreted a date such as 04/05/1990 as the intended month and day, since regional date settings can reverse that meaning.

Nothing changes after a birthday. Confirm that the formula uses TODAY() rather than a date typed directly into it, and that calculation is set to Automatic. If the workbook is in Manual calculation mode, recalculate it or switch to Automatic. A saved, exported, or copied value-only report will not update because it no longer contains the formula.

Frequently Asked Questions

Does the formula calculate age in years or a decimal?
It returns completed whole years. Someone who is 36 years and several months old will display as 36, not as a decimal such as 36.4.

Will the age update automatically on a birthday?
It updates when Excel recalculates the workbook because TODAY() provides the current date. If calculation is set to Manual, recalculate or switch the workbook to Automatic.

Can I calculate age as of a past or future date?
Yes. Put the chosen date in a cell and reference that cell as the end date in DATEDIF. Lock the reference with dollar signs if you plan to fill the formula down, as shown in the steps.

Why can’t I find DATEDIF in Excel’s function suggestions?
DATEDIF is supported in Excel but may not appear in the formula autocomplete list. Type the formula directly, making sure the start date comes before the end date and the unit is entered as “Y”.

Can I use this in Excel for Mac or Excel for the web?
Yes. The DATEDIF and TODAY functions work in current Excel for Mac and Excel for the web as well as Excel for Microsoft 365 on Windows. The formula is the same; ribbon placement and some keyboard shortcuts can differ.

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.