How to Calculate Age in Excel From a Date of Birth

To calculate age in completed years in Excel, put the date of birth 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 time passes. Before you start, make sure the birth date is stored as a real Excel date, not as text.

What You’ll Need

A Microsoft Excel workbook in Excel for Microsoft 365 on Windows. The same DATEDIF formula works in Excel for Mac and Excel for the web.
A column containing dates of birth, with one person’s date in each row. For the examples below, the first date is in cell A2.
An empty column for the calculated ages. If you want ages as of a chosen date rather than today, you will also need a cell containing that date.

Step-by-Step

Set up the date-of-birth column. Put a clear heading such as Date of Birth in A1, then enter the first person’s birth date in A2. Put additional dates in A3, A4, and the rows below. Use an unambiguous date format, such as 15-Jun-1990, or enter dates in a format Excel recognizes for your Windows regional settings. To check that Excel recognizes a value as a date, select the cell and look at the Number Format box on the Home tab; a recognized date can be displayed using a date format.
Add an age heading. Enter Age in B1, or choose another empty column. Keeping the result beside the birth date makes it easier to check each row. If the worksheet has other data, use a heading that describes the result, such as Age as of Today.
Enter the age formula for the first person. With the date of birth in A2 and the result going in B2, enter =DATEDIF(A2,TODAY(),"Y") in B2, then press Enter. DATEDIF compares the start date with the end date, and the “Y” unit asks for completed whole years. TODAY() supplies the current date according to the computer’s date, so the result changes when the workbook recalculates on a later day.
Check the result before filling the column. For example, if A2 contains 15-Jun-1990 and today is 2-Nov-2026, the formula returns 36. The person has passed their birthday in 2026. If their birth date were 15-Dec-1990, the result on that same day would be 35, because the birthday for 2026 has not arrived yet. This check helps catch reversed dates, incorrect years, or text values before you copy the calculation to many rows.
Copy the formula to the other rows. Select B2 and point to the small square at the lower-right corner of the selected cell, called the fill handle. Drag it down alongside the list, or double-click the fill handle to fill down next to a continuous list of dates. Excel adjusts the row reference automatically: the formula in B3 will refer to A3, and the one in B4 will refer to A4. If there are blank rows or unrelated data beside the list, dragging to the exact last row gives you more control.
Use a chosen date instead of today, if needed. This is useful for reports that must show age on a particular date, such as the start of a school year or the date a policy began. Put the chosen date in B1 and the date of birth in A2. Put the result in C2 and enter =DATEDIF(A2,B$1,"Y"). For example, if A2 is 15-Jun-1990 and B1 is 2-Nov-2026, C2 returns 36. The dollar sign keeps the reference on row 1 when you fill the formula down; the birth-date reference changes to A3, A4, and so on. Format B1 as a date so the chosen date is clear to anyone reading the sheet.
Keep empty rows from showing errors or unwanted results. If your list may contain blank birth-date cells, put this blank-safe version in B2 instead of the basic formula. With dates of birth in A2:A20 and ages in B2:B20, enter =IF(A2="","",DATEDIF(A2,TODAY(),"Y")) in B2 and fill it down. The formula leaves B blank when A is blank, while still calculating age for populated rows. Check that the range you fill matches the length of your list.
Choose how to display the result. Age in completed years is a whole number, so General or Number format is suitable. If Excel displays a date or an unexpected decimal, select the age cells and choose Home > Number Format > General or Number. Do not add a date format to the age results; the calculation is a count of years, not another date.

Common Mistakes

Using YEAR minus YEAR. Subtracting the birth year from the current year does not check whether the person’s birthday has happened yet. It can overstate age by one. DATEDIF with the “Y” unit accounts for the month and day as well as the year, so it returns completed years.
Entering the birth date as text. A value that looks like a date may be stored as text after being pasted from a website or imported file. DATEDIF may then return an error or fail to calculate as expected. Re-enter the date in a recognized format, or use Text to Columns to convert imported values when appropriate. Check the cell’s format and test an individual value before changing a large range.
Reversing the start and end dates. DATEDIF expects the earlier birth date first and the later comparison date second. If the date of birth is accidentally in the future, or the formula’s arguments are reversed, the formula can return a number error. Confirm both the data and the order of the references.
Expecting a decimal age. The “Y” unit returns whole completed years, not years plus a fraction for months or days. That is usually the right answer for an age field. If a report genuinely needs a more precise elapsed-time figure, define whether it should be measured in days or as a decimal year before choosing a different calculation.
Confusing the result with a fixed value. TODAY() changes as dates advance and Excel recalculates. A saved report that must preserve the age on its reporting date should use a chosen date in a cell instead of TODAY(), and keep that date with the report.

Tips

Convert the list into an Excel table by selecting the data and choosing Insert > Table. A formula entered in a table column usually fills down automatically when new rows are added, making a growing list easier to maintain.
Use a separate, clearly labelled comparison-date cell for recurring reports. The formula can keep calculating against that one date, and readers can see exactly what date the ages refer to.
For a report that must not change over time, copy the age results and use Paste Special > Values in a separate reporting copy. Keep the original formulas in the working file if you still need ages to update.
When sharing the workbook, explain whether the ages are calculated as of today or as of a specified date. This small note prevents readers from treating a live result as a permanent snapshot.
Excel for Mac and Excel for the web use the same DATEDIF and TODAY functions. The menus and ribbon layout can differ slightly by platform, but you can enter the formula directly in the cell in each version.

Troubleshooting

The formula shows #NUM!. Check that the date of birth is earlier than the comparison date. If you are using a date in a cell, verify that it is the intended date and that the formula places the birth-date cell first. A future birth date cannot produce an age in completed years as of today.

The formula shows #VALUE! or will not calculate. The likely cause is a date stored as text, an invalid date, or a typo in the formula. Click the birth-date cell and inspect its value in the formula bar. Re-enter a valid date, then check that the formula uses commas between arguments and straight double quotation marks around Y. If the cell contains an apostrophe before the date, Excel may be treating it as text.

The age is one year too high or too low. Compare the month and day in the birth date with the comparison date. Someone born later in the calendar year may not have had their birthday yet. Also check that the date has not been interpreted in the wrong order: for example, a value intended as day/month/year can be read as month/day/year under different regional settings.

The result appears as a date or has decimal places. Select the result cells and choose Home > Number Format > General. A date number format can make a whole-number age look like a calendar date, while a decimal format may display unnecessary digits. Changing the display format does not change the underlying age calculation.

The age does not update. TODAY() updates when Excel recalculates. Check that calculation is set to Automatic under Formulas > Calculation Options > Automatic. If calculation is set to Manual, press F9 on Windows to recalculate. On a Mac, use Fn+F9 where the function keys control system features, or use the Formulas menu’s calculation command. Excel for the web recalculates formulas in the workbook as it updates.

Frequently Asked Questions

Does DATEDIF work in Excel for Microsoft 365?
Yes. DATEDIF works in Microsoft 365, Excel for Windows, Excel for Mac, and Excel for the web. It is an older worksheet function that may not appear in Excel’s function autocomplete, but you can type it directly into a cell.

Does the formula calculate age in months or days too?
The “Y” unit gives completed years. DATEDIF also supports other interval units, but choose a unit only when it matches the reporting need. For most everyday age lists, completed years are the clearest result.

Can I calculate someone’s age on a date other than today?
Yes. Put the comparison date in a cell and use that cell as the formula’s end date, as shown in the steps. This is preferable to TODAY() for historical reports or a recurring report that needs a consistent cutoff date.

What happens for someone born on February 29?
The formula compares the recorded birth date with the comparison date and returns completed years. How an organization treats a leap-day birthday in a non-leap year can depend on its policy or local rules, so confirm the required convention if the result is used for eligibility or legal decisions.

Can I show age as years and months?
Yes, but that requires a different display calculation and a decision about how months should be reported. The basic formula here intentionally returns only completed years, which avoids implying that a decimal is a precise age in years and months.

Why is DATEDIF missing from the function list?
DATEDIF is supported even though Excel may not offer it in the usual autocomplete suggestions. Type the function directly, check that the parentheses and quotation marks are balanced, and confirm that the birth date is earlier than the end date.

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.