Calculate Age in Excel from DOB with a Simple Formula

If the date of birth (DOB) is in A2, enter =DATEDIF(A2,TODAY(),"Y") in another cell to return age in completed years as of today. Format the DOB as a real Excel date first; the result updates when Excel recalculates the workbook.

This approach is useful for a staff list, membership register, school records, or any worksheet where you need whole-year ages rather than a rounded estimate. The formula counts birthdays that have passed, so someone whose next birthday is still ahead remains at their current age. The steps below also show how to calculate age on a chosen date, keep blank rows tidy, and diagnose common errors.

What You’ll Need

A worksheet containing dates of birth, preferably in one column with a heading such as DOB.
A blank column for the age results. The examples use DOBs in column A and ages in column B, starting on row 2.
Excel for Microsoft 365 on Windows. The DATEDIF and TODAY functions used here are also available in Excel for Mac and Excel for the web.

You do not need an add-in or a special age-calculation setting. The important preparation is making sure Excel recognizes each DOB as a date value, rather than as text that only looks like a date.

Step-by-Step

Set up the DOB column. Put a clear heading such as DOB in A1 and enter each birth date below it, one per row. Keep the dates in a consistent format, for example 6/15/1990 or 15-Jun-1990. The format shown depends on your Windows regional settings, so use a style that is unambiguous for the people who will read the file. If you import dates from another system, check that Excel has interpreted them as dates instead of text.
Add an age heading. Enter Age in B1, or choose another empty column. Keeping the result separate from the DOB makes it easier to sort, filter, or review the original data without overwriting it.
Enter the age formula for the first record. With the DOB in A2 and the result going in B2, type =DATEDIF(A2,TODAY(),"Y") in B2 and press Enter. DATEDIF compares the start date with the end date, and the “Y” unit asks for the number of complete years between them. TODAY() supplies the current date, so a person is not counted as a year older until their birthday has arrived.
Check the first result. For example, with 15-Jun-1990 in A2, the formula returns 36 on November 2, 2026: the person turned 36 on June 15, 2026, and their 37th birthday has not arrived. If the first result looks wrong, check the DOB itself and the computer’s date before filling the formula down.
Copy the formula to the remaining rows. Select B2 and drag the small square at the lower-right corner of the selection down through the rows that contain DOBs. You can also copy B2, select the destination cells, and paste. Excel adjusts the relative reference for each row: the next row uses A3, then A4, and so on. In a long continuous list, double-clicking the fill handle often fills down to match the adjacent data.
Use an as-of date when you need a fixed reference date. A TODAY-based age changes over time. For an eligibility review or report that must be reproducible, put the chosen reference date in B1 and make sure it is an Excel date. With the DOB in A2, the as-of date in B1, and the result in C2, enter =DATEDIF(A2,$B$1,"Y"). The dollar signs keep the reference fixed when you copy the formula down. If A2 is 15-Jun-1990 and B1 is 2-Nov-2026, C2 returns 36. Changing B1 recalculates all the ages for that date.
Keep empty DOB rows empty in the results. If your list has gaps, a plain DATEDIF formula can return an error for a blank start date. With DOBs in A2:A100 and the age result in B2, use =IF(A2="","",DATEDIF(A2,TODAY(),"Y")). It returns an empty display when A2 is blank and an age when A2 contains a valid date. Copy it down the result column as before. This formula does not validate a nonblank entry, so check that every filled DOB is a genuine date.
Format and review the output. Age in whole years is normally best displayed as a number with zero decimal places. If Excel displays a date or an unexpected decimal, select the result cells, then use Home > Number Format and choose General or Number. Compare a few ages with known birthdays, especially dates near the beginning or end of the year, before relying on the entire column.

The same formulas work in Excel for Mac and Excel for the web; the buttons and window layout may differ slightly from the Windows ribbon. In installations configured for a different list separator, Excel may show a semicolon instead of a comma when displaying formulas. Use the separator Excel expects in your regional configuration.

Common Mistakes

Using subtraction and dividing by 365. A formula that divides the number of days by 365 does not reliably calculate age. Calendar years vary in length because of leap years, and the result can change before a birthday. DATEDIF with the “Y” unit counts complete years instead.
Using the wrong DATEDIF unit. The unit needs to be in quotation marks. “Y” returns completed years; it is not the same as “M” for complete months or “D” for days. For ordinary age in years, use the exact “Y” unit shown in the steps.
Including the heading in the formula range. If the first DOB is in A2, the first formula should refer to A2, not A1. Referring to a heading such as DOB causes a calculation error because the heading is not a date.
Typing a DOB as text. A date copied from a website or imported from a text file might look right but fail in DATEDIF. Text can also be interpreted differently depending on regional date conventions. Convert or re-enter it as a date, then check whether Excel recognizes it as a date value.
Expecting the result to include a partial year. The “Y” unit deliberately returns whole completed years. A person who is 36 years and 11 months old still has an age result of 36. Do not format the result as a percentage or add decimal places if your goal is a standard age in years.
Forgetting that TODAY changes. An age based on TODAY() is dynamic, not a permanent snapshot. It can update when the workbook recalculates or is opened on a later date. Use a date entered in a separate as-of cell for reports that must preserve the age at a specific point in time.

Tips

Make the reference date easy to audit. For a report, label the fixed date cell clearly, such as Age calculated as of, and record the date beside the table. This lets another reader see exactly what the ages mean instead of assuming they represent today.
Use an Excel Table for a growing list. Select the data and choose Insert > Table, ensuring the table has headers. When you type a formula in the age column, Excel usually fills it throughout that table column and applies it to new rows. This is convenient for regularly updated registers, although you should still verify the formula on the first few records.
Keep DOBs and ages in separate columns. Retaining the original DOB makes the calculation transparent and allows you to recalculate age for another date. Avoid replacing birth dates with ages alone; that discards the information needed to update or verify the result.
Check boundary dates. Test a DOB whose birthday is today and one whose birthday is tomorrow. The first should show the age reached today; the second should show one year less until tomorrow. For a February 29 DOB, confirm the organization’s rule for when that birthday is treated as occurring in non-leap years if the exact legal or policy interpretation matters.
Use a clear number format. The result is a count of years, not a date. A number format with zero decimal places is usually appropriate. You can add a nearby label such as Age (years) so the meaning is clear when someone exports or prints the worksheet.

Troubleshooting

DATEDIF returns #NUM!: The start date is later than the end date, which commonly means the DOB is in the future or the formula points to the wrong cell. Confirm the DOB and the reference date. If you are calculating against a fixed date, verify that the date in the as-of cell is on or after the DOB.

DATEDIF returns #VALUE!: One of the date inputs may be text, blank in a formula that does not handle blanks, or otherwise invalid. Select the DOB cell and inspect the formula bar. Re-enter a valid date directly, or convert imported text using the appropriate date-conversion method for its format. A date that is left-aligned can be a clue that it is text, though alignment alone is not conclusive because cell formatting can change alignment.

The result is unexpectedly large or small: Check the date order and the cell references. In some regional settings, 4/5/1990 means April 5; in others it means May 4. Use an unambiguous entry such as 5-Apr-1990 when entering dates manually. Also confirm that the result formula refers to the DOB on the same row and that a fixed as-of date uses an absolute reference such as $B$1.

The result appears as a date or as ####: The formula may be correct while the cell has an unsuitable number format or is too narrow. Choose General or Number from Home > Number Format and widen the column if needed. A row of hash marks often means the column is not wide enough to show the formatted value.

The age does not change when expected: Check that the formula contains TODAY() and that the workbook is recalculating. If Excel is set to manual calculation, use Formulas > Calculation Options > Automatic. A fixed date typed into a cell will not move forward on its own; change that cell when you want a different as-of date.

Frequently Asked Questions

Does DATEDIF calculate age accurately in completed years?
Yes. With a valid DOB before or on the end date, DATEDIF using “Y” returns the number of full years completed. It does not round up based on how close the next birthday is.

How do I calculate age on a date other than today?
Enter the chosen date in a cell, such as B1, then use =DATEDIF(A2,$B$1,"Y") when the DOB is in A2. The dollar signs keep B1 fixed as you copy the formula to other rows.

Why does Excel not suggest DATEDIF while I type?
DATEDIF is a supported legacy worksheet function, but Excel may not show it in the same way as functions that appear in formula suggestions. Type the formula directly, including the quotation marks around “Y”.

Can I calculate age in months or days instead?
DATEDIF supports other interval units, but they answer a different question from age in whole years. “M” gives complete months and “D” gives days between dates. For a standard age, use “Y”; for reporting months and remaining days, plan the calculation and labels carefully so the result is not mistaken for a whole-year age.

Will the formula work in Excel for Mac or Excel for the web?
Yes. DATEDIF and TODAY are available in those versions as well as Excel for Microsoft 365 on Windows. Ribbon locations and regional separators can differ, but the calculation logic is the same.

Can I keep the age from changing after I calculate it?
Use a fixed as-of date in a cell instead of TODAY(), and keep that date unchanged. If you need a permanent snapshot rather than a formula, copy the results and use Paste Special > Values, while retaining the original DOB and recording the date used for the calculation.

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.