Calculate Age in Excel From a Date of Birth

To calculate someone’s age in completed years in Excel, enter their date of birth in A2 and use =DATEDIF(A2,TODAY(),"Y") in B2. Excel compares the birth date with today’s date and returns the number of full years. You’ll need a real Excel date value for the birth date; the result updates when the workbook recalculates.

What You’ll Need

A date-of-birth column, with one person’s birth date in each row. For the examples below, the first date is in A2.
A blank column for the age result, such as column B.
Microsoft Excel for Microsoft 365 on Windows. The formulas in this guide also work in current desktop Excel for Mac and Excel for the web, though ribbon layouts may differ slightly.

Use actual dates rather than text that only looks like a date. If you are working with a list, keep a separate header row—for example, “Date of birth” in A1 and “Age” in B1—and put the first person’s data in row 2.

Step-by-Step

Enter and check the date of birth. Put a birth date in A2, such as 15 June 1990. Select the cell and choose Home > Number Format > Short Date if you want to display it as a date. The exact appearance depends on your regional settings, so Excel might show the same date as 6/15/1990 or 15/06/1990. Check that Excel recognizes the entry as a date, not as text; changing the number format alone will not convert text into a real date.
Choose where the age should appear. With the birth date in A2, use B2 for the result. Add a clear header in B1, such as “Age in years.” Keeping the result in its own column makes it easier to fill the formula down, sort the list, and use the ages in reports.
Enter the completed-years formula. With the birth date in A2 and the age result in B2, enter =DATEDIF(A2,TODAY(),"Y") in B2 and press Enter. DATEDIF compares the start date with the end date, and the “Y” unit asks for complete years only. For a birth date of 15 June 1990, the formula returns 36 on 2 November 2026, because the person has had their 36th birthday and not yet their 37th. The answer changes as time passes because TODAY() returns the current date.
Read the result as a whole-year age. DATEDIF with the “Y” unit does not round to the nearest year. It counts birthdays already reached by the comparison date. Someone who is one day short of their birthday remains the age they were before that birthday. This is normally the expected meaning of age in years for a roster or form, but it is different from an approximate age that includes a decimal fraction.
Apply the formula to more rows. If the dates of birth are in A2:A20 and the age results belong in B2:B20, enter the formula in B2, select B2, and drag the small fill handle at the bottom-right corner of the cell down to B20. Excel adjusts each row reference, so the formula in B3 uses A3, the formula in B4 uses A4, and so on. For a long, uninterrupted data list, double-clicking the fill handle usually fills down alongside the neighboring data. Check the final row to ensure it was included.
Keep empty rows empty, if needed. If your list has blank cells in the date-of-birth column, a plain formula may display an unexpected result for those rows. With birth dates in A2:A20 and age results in B2:B20, use =IF(A2="","",DATEDIF(A2,TODAY(),"Y")) in B2, then fill it down. This checks whether A2 is blank before calculating; if it is, the result cell displays nothing. It does not hide invalid dates, so check any error values separately.
Use a fixed comparison date when appropriate. If you need ages as of a particular date rather than today, put that date in B2 and the age result in C2. With a birth date in A2 and an as-of date in B2, enter =DATEDIF(A2,B2,"Y") in C2. For a birth date of 15 June 1990 and an as-of date of 2 November 2026, the result is 36. This setup is useful for an audit, application deadline, or report that must remain tied to a past date; unlike TODAY(), the date in B2 will not change on its own.
Optionally show years and remaining months. With the birth date in A2 and today’s date as the comparison date, enter =DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months" in B2. For a birth date of 15 June 1990 on 2 November 2026, this displays “36 years, 4 months.” The “YM” unit gives the remaining months after full years; it does not show the number of days since the last monthly anniversary. Use this only when a years-and-months display is more useful than a single age number.

Common Mistakes

Using YEAR subtraction by itself. Subtracting the birth year from the current year ignores whether the birthday has happened yet. For example, on 2 November 2026, someone born on 15 December 1990 is still 35, not 36. DATEDIF with the “Y” unit accounts for the birthday.
Putting the dates in the wrong order. DATEDIF expects the earlier date first and the later date second. The birth date belongs in the first argument; the current or as-of date belongs in the second. Reversing them can cause an error.
Expecting a decimal age. The “Y” unit returns completed years, not a value such as 36.4. If you need an approximate decimal for a statistical calculation, choose a method suited to that purpose and document how it defines a year; a decimal age is not the same as a completed-years age.
Confusing display with stored value. A date can be displayed as a number if the cell is formatted as General, and a text date can remain text even after you apply a date format. Confirm the cell contains an Excel date value before troubleshooting the formula.
Not deciding how to treat leap-day birthdays. People born on 29 February may be treated as having a birthday on 28 February or 1 March in a non-leap year, depending on the organization’s rules or the relevant legal context. Agree on the rule if the age is used for eligibility, compliance, or another consequential decision, then verify boundary cases against it.

Tips

Use an as-of date for repeatable reports. TODAY() is convenient for a live list, but it changes with the date. For a report that must reproduce the same ages later, store the report’s comparison date in a clearly labeled cell and reference that cell in the DATEDIF formula.
Format the result column intentionally. The whole-year formula returns a number, so a General or Number format is appropriate. If you want the word “years” displayed without turning the result into text, select the result cells and use Home > Number Format > More Number Formats to set a custom format such as 0 "years". The underlying result remains numeric, which is helpful for sorting and calculations.
Make the date source unambiguous. Dates such as 04/05/2000 can mean different days in different regional settings. When sharing a workbook, agree on a date convention or enter dates in a way Excel recognizes consistently, then check a few records after importing data.
Use a table for a growing list. Select the data and choose Insert > Table to make a structured list. When you add new records to the table, Excel can extend a calculated-column formula automatically. Keep the date-of-birth and age headers descriptive so other users can tell which date the age is based on.
Review exceptional records separately. A future birth date may produce an error or indicate incorrect source data. A person’s age also depends on the comparison date and the agreed birthday convention. For official decisions, verify the original date and applicable rules instead of relying on a spreadsheet result alone.

Troubleshooting

The formula shows #NUM!: DATEDIF commonly returns this error when the start date is later than the end date. Check whether the birth date is in the first argument and TODAY() or the intended as-of date is in the second. Also check for a future birth date or a mistakenly entered year.

The formula shows #VALUE! or does not calculate: One of the cells may contain text rather than a valid date. Select the date cell and inspect the formula bar and number format. If the source came from a text file, use Data > Text to Columns to convert dates with the correct date order, or re-enter a test date directly into a blank cell. Then update the formula reference if necessary.

The result seems one year too high: Check whether the formula subtracts years directly rather than using DATEDIF with “Y”. Also verify the current date on the computer if TODAY() is involved, and confirm the birth date’s month and day were interpreted correctly. A birthday that has not yet occurred this year should not increase the completed-years age.

The result does not change: TODAY() updates when Excel recalculates. In Excel for Microsoft 365 on Windows, check Formulas > Calculation Options and select Automatic if calculation is set to Manual. You can also recalculate the workbook. Excel for Mac has calculation options under Formulas as well, although the interface can vary by release; Excel for the web generally recalculates automatically. A fixed as-of date entered in a cell will not update unless you change it.

The formula appears as text in the cell: The result cell may be formatted as Text, or there may be an apostrophe before the equals sign. Set the cell to General, edit the formula, and press Enter. If Excel displays the formula rather than its answer across many cells, check whether Show Formulas is enabled on the Formulas tab.

Frequently Asked Questions

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

Does DATEDIF update automatically?
When its end date is TODAY(), the result can update as the current date changes and Excel recalculates. It will not behave as a live age if you replace TODAY() with a fixed date in a cell.

Can I calculate age on a past date?
Yes. Put the desired comparison date in a cell and use it as DATEDIF’s second argument. That keeps the calculation tied to the selected date rather than today.

Can I show age in years and months?
Yes. Combine DATEDIF with the “Y” and “YM” units, as shown in the steps. The result is text, so use the single-number formula instead if you need to sort or calculate with age as a number.

Why does DATEDIF not appear in Excel’s function suggestions?
DATEDIF is an older compatibility function and may not appear in the autocomplete list. It is still available in Excel; type the formula directly and check that the start date comes before the end date.

Will the formula work in Excel for Mac or the web?
Yes. DATEDIF, TODAY, and IF are available in those versions too. The ribbon placement and calculation settings can look different, but the formulas and comma separators shown here are the same in standard English-language Excel installations.

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.