Calculate Age in Excel From Two Dates: Exact Years

To calculate someone’s age in completed years from a birth date and a second date, enter the birth date in A2 and the later date in B2, then use =DATEDIF(A2,B2,"Y"). Excel returns the number of full years between the dates, so the result changes only after the birthday has passed. You can use today’s date or any chosen as-of date.

What You’ll Need

A Microsoft Excel workbook. These instructions are for Excel for Microsoft 365 on Windows; the formula also works in current Excel for Mac and Excel for the web.
A cell containing the birth date and a cell containing the date through which you want to calculate age. The dates must be valid Excel dates, not text that merely looks like a date.
An empty cell for the result. For a list, arrange the birth dates in one column and the corresponding as-of dates in another.

Step-by-Step

Set up the two dates. In a blank worksheet, type a label such as Birth date in A1 and As-of date in B1. Enter the birth date in A2 and the date you want to measure age on in B2. For example, enter June 15, 2000 in A2 and June 14, 2024 in B2. Use dates in chronological order: the birth date must be earlier than or equal to the as-of date. If Excel displays a number instead of a familiar date, the value may still be a valid date; Excel stores dates as serial numbers.
Make sure Excel recognizes the entries as dates. Select A2 and B2 and choose Home > Number Format, then choose a date format. You can also use the keyboard shortcut Ctrl+1 on Windows to open Format Cells and select a date format. On Mac, use Command+1 to open Format Cells. Formatting changes how a valid date appears; it does not turn text into a valid date. If an entry stays left-aligned or changing its format has no effect, re-enter it in a date format Excel recognizes for your regional settings.
Choose the result cell. Click C1 and type Age (years), then click C2. Keeping the result in a separate column makes it easy to check the inputs and copy the calculation for other people. The example assumes the birth date is in A2 and the as-of date is in B2.
Enter the age formula. With the birth date in A2 and the as-of date in B2, enter =DATEDIF(A2,B2,"Y") in C2 and press Enter. DATEDIF calculates the interval between the two dates; the unit “Y” asks for the number of complete years. With June 15, 2000 in A2 and June 14, 2024 in B2, the result is 23, because the 24th birthday has not yet occurred on the as-of date.
Check the birthday boundary. Keep A2 as June 15, 2000, but change B2 to June 15, 2024. The same formula returns 24: the person has reached the 24th birthday on that date. If B2 is June 16, 2024, the result remains 24. This is a useful check that the calculation counts completed years rather than simply subtracting the birth year from the other year.
Calculate age for today instead. If you want age as of the current date and do not need a manually selected as-of date, you can use a formula that supplies today’s date automatically. With the birth date in A2, enter =DATEDIF(A2,TODAY(),"Y") in the result cell. TODAY returns the current date according to Excel’s date system and updates when the workbook recalculates, so this result can change over time. Keep a fixed date in B2 when you need a stable historical calculation, such as someone’s age on the day a form was submitted.
Copy the formula down for more rows. For a list with birth dates in A2:A20 and matching as-of dates in B2:B20, first enter the formula for row 2 using A2 and B2. Select C2 and drag the small fill handle at the lower-right corner of the selection down through C20. Excel adjusts the row references for each copied formula, so the next row uses A3 and B3, and so on. On Windows, you can also select C2:C20 and press Ctrl+D to fill down; on Mac, the equivalent shortcut is Command+D. Confirm that each row’s birth date is paired with the intended as-of date.
Format and review the output. Age in completed years is a whole number, so the General or Number format is usually suitable. If the cell shows a date, select the result cells and choose Home > Number Format > General or Number. Check a few rows manually, especially dates close to birthdays, and verify that no as-of date comes before its birth date. Save the workbook after checking the results.

Common Mistakes

Subtracting birth years only. A calculation that subtracts the birth year from the as-of year can be one year too high when the birthday has not yet happened. For example, someone born late in the year may still be one year younger than that subtraction suggests in the months before their birthday. DATEDIF with the “Y” unit checks whether a full year has elapsed.

Reversing the date arguments. DATEDIF expects the earlier date first and the later date second. Put the birth date in the first reference and the as-of date in the second. If the as-of date is earlier than the birth date, Excel returns an error rather than a meaningful age.

Using dates stored as text. A value such as a date copied from a website or imported file may look like a date but be stored as text. Changing the number format alone may not fix it. Re-enter the date, or convert the imported values to real dates before calculating.

Expecting a decimal age. The “Y” unit returns whole completed years, not years plus a fraction. That is generally the intended meaning of a person’s age. A decimal result uses a different convention and can depend on how days and leap years are counted, so it should not be substituted without confirming what the calculation is meant to represent.

Confusing DATEDIF’s units. The “Y” unit is for completed years. Other DATEDIF units answer different questions, such as the total number of months or days in an interval. Do not replace “Y” with another unit unless that is the measure you actually need.

Tips

Use an explicit as-of date for reports, snapshots, and records that must not change later. If you use TODAY, the displayed age is dynamic: it can update on a later day when Excel recalculates. For a report that must preserve the age as it was on a particular date, put that date in a dedicated cell and keep it with the workbook.

Label the date columns clearly, and use one date per cell. Avoid combining a person’s name and birth date in the same cell, since separate columns are easier to sort, filter, validate, and reference. If your workbook contains many records, keep the same column layout throughout so each formula consistently pairs the right birth date with its matching as-of date.

For a simple audit, test one person whose birthday has already occurred this year and another whose birthday is still coming up. Also check a birthday itself: the result should increase on the birthday, not the day before. These examples quickly reveal a formula that is only subtracting calendar years or referring to the wrong date column.

DATEDIF is a long-standing Excel worksheet function, although it may not appear in the function autocomplete list while you type. Enter it directly in the cell, including the opening and closing parentheses and the quoted unit. Excel formula examples here use commas between arguments. If your regional settings require semicolons, Excel may display or accept semicolons as the argument separator instead; use the separator your installation expects.

Troubleshooting

The formula returns #NUM!. Check that the first referenced date is earlier than or equal to the second. For an age calculation, confirm the birth date is in the first argument and the as-of date is in the second. Also inspect the year values in both cells in case a date was entered with the wrong year.

The formula returns #VALUE! or does not calculate. Check that both referenced cells contain real dates rather than text, and confirm that the formula begins with an equals sign and has straight quotation marks around Y. If Excel shows the formula itself in the cell instead of a result, the cell may be formatted as Text. Change it to General, then edit the cell and press Enter again. Also make sure Show Formulas is not enabled; on Windows, Ctrl+` toggles that view. The key is usually near the top-left of the keyboard.

The result is unexpectedly one year too high or low. Verify the exact month and day in both date cells, not just the year. Compare the as-of date with the birthday for that year. A person born on December 10 is still one year younger on December 9 than on December 10, even though the calendar year is the same in both cases.

The date appears as a large number. Excel stores dates as serial values, so a number may indicate that the cell is a date shown in General format. Select the input cell and choose a date format from Home > Number Format. For the calculated age, use General or Number instead. If the number is negative or implausible, inspect the input values rather than relying on formatting to correct them.

Copied rows show the wrong age. Check that each formula refers to the date cells on the same row. When copying down from C2, the row references should advance to the next row. Look for blank or shifted dates, and confirm the birth date and as-of date columns have not been sorted separately. If you are using TODAY, remember that changes in the result over time are expected.

Frequently Asked Questions

Does DATEDIF calculate age correctly on a birthday?

Yes. With the birth date as the first date and the birthday as the second date, DATEDIF using the “Y” unit counts the newly completed year. The result is still the previous age on the day before the birthday.

Can I calculate age on a date other than today?

Yes. Put the chosen as-of date in a cell and use that cell as the second date reference. This is more reliable than using TODAY when you are calculating age at a past event or need to keep a report fixed to a particular date.

Can I calculate age using only the birth date?

Yes, if the intended as-of date is today. Use the version with TODAY as the second date. It updates as the workbook recalculates. If you need an age that stays fixed, use a date entered in a cell instead.

Why is DATEDIF missing from Excel’s formula suggestions?

DATEDIF may not appear in autocomplete, but you can type it directly into a cell. Check the spelling, parentheses, cell references, and quotation marks if Excel reports a formula error.

How do I calculate age for a whole column of people?

Put each person’s birth date and as-of date on the same row, enter the formula for the first row, then copy it down with the fill handle or the fill-down shortcut. Review the row references and pairings before relying on the full set of results.

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.