Calculate Age in Excel Using Date of Birth with DATEDIF

To calculate age in Excel from a date of birth, put the birth date in A2 and enter =DATEDIF(A2,TODAY(),"Y") in another cell. The result is the number of complete years as of today, and it updates when Excel recalculates. You will need a real Excel date value for each date of birth; the formula will not work reliably with dates stored as text.

What You’ll Need

A Microsoft Excel workbook. These steps are for Excel for Microsoft 365 on Windows; the formula also works in Excel for Mac and Excel for the web.
A column containing dates of birth, with one person per row. The examples use A2 for the first date.
A blank column where you can display the calculated age.

Step-by-Step

Enter the dates of birth. Put a clear heading such as Date of Birth in A1, then enter each person’s date in A2, A3, and the rows below. Excel must recognize each entry as a date, not as a text string. A dependable way to enter one is to type a date in a format Excel recognizes in your regional settings, such as 15-Aug-1990, then press Enter. If you use numeric dates such as 03/04/1990, check whether your system reads that as March 4 or April 3. Select the date cells and choose Home > Number Format to display them consistently, for example as a short date. Formatting changes how a value appears; it does not turn text into a date.
Add a heading for the result. Enter Age in B1, next to the date-of-birth heading. Keeping the result in a separate column preserves the original birth date and makes the sheet easier to review, sort, or filter.
Enter the whole-years formula. With a date of birth in A2, enter =DATEDIF(A2,TODAY(),"Y") in B2. DATEDIF compares the birth date with today’s date, while the “Y” unit asks for completed years. For a birth date of 15-Aug-1990, the result is 36 on 2-Nov-2026: the person’s 36th birthday has passed, but the 37th has not. Your result will differ when TODAY() advances, so treat that example as a check of the arithmetic rather than a fixed answer.
Confirm the cell displays a whole number. Click B2 and look at the formula bar to confirm that the formula is present. If the result appears as a date or has unwanted decimal places, select B2, then choose Home > Number Format > General or Number. For a simple age, Number with zero decimal places is also suitable. This affects display, not the underlying calculation.
Copy the formula down the column. Select B2 and drag the small square at the lower-right corner of its selection (the fill handle) down to the last row. You can also copy B2, select the destination cells, and paste. Excel adjusts the reference from A2 to A3, A4, and so on. Check a couple of rows after filling to make sure each age is beside the correct date of birth.
Optionally keep blank rows blank. If your list has empty rows, use this version instead. With dates of birth in A2:A100 and the age result starting in B2, enter =IF(A2="","",DATEDIF(A2,TODAY(),"Y")) in B2, then fill it down. The IF test returns an empty display when A2 is empty, rather than calculating from Excel’s zero-date value. A nonblank cell containing text or an invalid date can still produce an error, so this formula is not a substitute for checking the source data.
Choose whether the age should keep changing. TODAY() is useful for a live age list: Excel updates it when the workbook recalculates, usually when the file opens or a calculation occurs. For a report that must preserve an age as of a particular day, use an explicit as-of date in a cell instead. With the birth date in A2 and the chosen as-of date in B1, enter =DATEDIF(A2,B1,"Y") in C2. For example, if A2 is 15-Aug-1990 and B1 is 2-Nov-2026, C2 returns 36. Keep the as-of date visible so readers know what the result means.

DATEDIF also supports a years-and-months display when you need more detail than a whole-number age. With the date of birth in A2, enter =DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months" in B2. For the example birth date 15-Aug-1990 on 2-Nov-2026, this displays 36 years, 2 months; it does not include the remaining days. The formula joins text with ampersands, so the output is a text label rather than a number to use directly in arithmetic. Use the whole-years formula if you need to sort or calculate with ages as numbers.

Common Mistakes

Using YEAR alone. Subtracting the birth year from the current year can overstate someone’s age before their birthday has arrived this year. DATEDIF with the “Y” unit checks whether the anniversary has passed and returns completed years.
Using the wrong DATEDIF unit. The unit must be text in straight double quotation marks. “Y” returns complete years; “YM” returns remaining months after complete years. “M” counts total complete months, not the month remainder. Use the unit that matches what the result is meant to say.
Entering dates as text or in the wrong order. Excel can interpret numeric dates differently according to regional settings. A value that looks like a date may still be text, or may represent a different day than intended. Confirm a few entries against the original records and use a clear display format.
Forgetting that TODAY() moves forward. A live age is expected to change over time. If you are producing a dated snapshot, use an as-of date in a cell instead of TODAY(), and record that date with the report.
Expecting age to include months or days. The basic formula returns an integer number of completed years. Choose the longer years-and-months formula if that is what the audience needs, and label the result so it cannot be mistaken for a decimal age.
Ignoring dates after the as-of date. A birth date later than TODAY(), or later than the as-of date, is invalid for an ordinary age calculation and can return #NUM!. Check for a typo or confirm that the date is genuinely intended to be in the future.

Tips

Use a fixed as-of date for repeatable reporting. If a monthly report is regenerated later, TODAY() will produce a different age. Put the report date in a labeled cell and reference that cell so the calculation can be reproduced.
Keep the birth-date column intact. Store full dates, including the year, rather than only a birth year or month and day. A full date is needed to determine whether the birthday has passed in the current year.
Use clear headings and consistent formats. Headings such as Date of Birth, Age (years), and Age As Of help prevent confusion when someone else reads the workbook. Apply the same date format to the full date column.
Be careful with February 29 birthdays. Excel calculates completed years from its date values, but legal or organizational rules may define the age-change date for leap-day birthdays differently in non-leap years. If the result is used for eligibility, compliance, or another formal decision, confirm the applicable rule rather than assuming a spreadsheet convention settles it.
Know that formulas recalculate. TODAY() is a volatile date function and can change after recalculation. Excel for Mac and Excel for the web use the same formula syntax, although the ribbon layout may differ slightly. In all versions, a fixed date reference is the safer choice when the answer must remain unchanged.

Troubleshooting

The cell shows #NUM!. Check that the birth date is not later than the comparison date. If you are using an as-of date in B1, confirm B1 is on or after the date in A2. Also check that the year, month, and day were entered correctly.

The cell shows #VALUE! or the result seems wrong. The date may be stored as text, or the formula may point to the wrong cell. Click the birth-date cell and inspect the formula bar; verify the date against the source record. If Excel treats it as text, re-enter it in a date format recognized by your regional settings, or use Data > Text to Columns to convert a consistently formatted text column. Review the conversion preview before completing it.

The formula appears as text instead of calculating. Select the formula cell and change its number format from Text to General. Then press F2 and Enter on Windows to re-enter the formula so Excel evaluates it. If the worksheet is showing formulas in every cell, turn off Formulas > Show Formulas. On a Mac, the ribbon command is also available under Formulas; keyboard shortcuts can differ by keyboard and Excel settings.

The formula is rejected when you enter it. Check that the function name is DATEDIF, the unit is inside straight double quotation marks, and the brackets are balanced. The formulas here use commas as argument separators. Some regional Excel settings use a different separator; if Excel highlights the commas as a problem, check the list-separator setting for that installation rather than changing the date logic.

The formula works but the age is a decimal or date. Select the result and choose Home > Number Format > Number, then set decimal places to zero. For a standard DATEDIF result, General also works. If it still looks wrong, inspect the formula bar: a cell format cannot fix a formula that references the wrong date.

Frequently Asked Questions

Is DATEDIF available in Excel for Microsoft 365?
Yes. DATEDIF works in Excel for Microsoft 365, although it may not appear in the function autocomplete list. Type the function directly and make sure the unit is quoted.

Does the age update automatically on a birthday?
The formula uses TODAY(), so it can show the new age after the date changes and Excel recalculates. It is not a live clock that changes every second. If the workbook does not refresh, recalculate it or reopen the file.

How do I calculate someone’s age on a past date?
Put that reference date in a cell and use it as DATEDIF’s second argument. With the birth date in A2 and the reference date in B1, enter =DATEDIF(A2,B1,"Y") in C2. The reference date must not be earlier than the birth date.

Can I calculate age in months instead of years?
Yes. With the birth date in A2, =DATEDIF(A2,TODAY(),"M") returns total completed months. This is different from the “YM” unit, which returns only the months remaining after complete years.

Why does DATEDIF not appear in Excel’s suggestions?
DATEDIF is supported but is not always listed by formula autocomplete or the Insert Function interface. Its absence from suggestions does not mean the function is unavailable. Enter it directly, with the correct references and quoted unit.

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.