Calculate Age in Excel in Years with Simple Formulas

To calculate completed age in years in Excel, put each birth date in column A and, with the birth date in A2, enter =DATEDIF(A2,TODAY(),"Y") in B2. The result is the person’s age in whole years as of today. You need a real Excel date in the birth-date cell; the result updates when the workbook recalculates.

This guide uses Microsoft Excel for Microsoft 365 on Windows. DATEDIF is a built-in compatibility function that works in current Excel even though it may not appear in formula suggestions. If you need an age as of a specific date rather than today, use that date in a separate cell instead. The steps below cover setup, copying the formula down a list, and fixing common date problems.

What You’ll Need

A worksheet with a column for birth dates. For example, enter a heading in A1 and the first birth date in A2.
An empty column for the age results, such as column B.
Birth dates stored as actual Excel dates, not text that merely looks like a date. You can type dates using the format normally recognized by your regional settings.
Optional: a cell containing a fixed reference date, if you want ages calculated as of a date other than today.

No add-in or special setting is required. DATEDIF works in Excel for Microsoft 365 on Windows and Mac, and in Excel for the web. The menu labels and formula-entry process are broadly similar across these versions, although some ribbon layout details can differ.

Step-by-Step

Set up the columns. Put a clear heading such as Birth date in A1 and Age in years in B1. Enter each person’s birth date below the first heading, one date per row. Keeping one birth date per cell makes it possible to copy a formula down and sort or filter the records later. Avoid including a person’s name in the same cell as their date.
Check that the birth date is a real date. Click a birth-date cell and look at the formula bar. Excel stores valid dates as numbers, even when it displays them in a friendly format such as 15-Aug-1990. If the entry is left-aligned or does not behave like a date, it may be text. You can select the date column and use Home > Number Format to choose a date display. Changing the display format does not convert text into a date, so verify that Excel recognizes the value before continuing.
Enter the age formula in the first result row. With birth dates in A2:A20 and the age result for the first record in B2, enter =DATEDIF(A2,TODAY(),"Y"). Press Enter. DATEDIF compares the start date in A2 with today’s date and the “Y” unit returns the number of complete years between them. For example, a birth date of 15-Aug-1990 produces 34 when the reference date is 2-Nov-2024: the person has had their 34th birthday, but not their 35th.
Fill the formula through the list. Select B2, point to the small square at the lower-right corner of the cell, and drag it down to the last row of birth dates. You can also double-click that square when the adjacent birth-date column has a continuous list of entries. Excel adjusts the reference from A2 to A3, A4, and so on. Check a few rows to confirm each result is beside the correct birth date.
Format and label the results. Whole-number ages are usually best displayed with the General or Number format and zero decimal places. Select the results, then use Home > Number Format to choose an appropriate format. DATEDIF with the “Y” unit returns a whole number, so decimal formatting is not needed. Make the heading clear, for example Age (years), so readers know the values are completed years rather than years with a fraction.
Use a fixed date when you need a historical or future age. Enter the reference date in C1 and label it Reference date. With birth dates in A2:A20 and the date to calculate against in C1, enter =DATEDIF(A2,C1,"Y") in B2, then fill it down. If C1 contains 2-Nov-2024 and A2 contains 15-Aug-1990, the result is 34. This approach is useful for a report or eligibility check tied to a specific date; it also prevents the answer changing tomorrow simply because TODAY() has advanced.
Protect the reference date while copying, if needed. In the fixed-date setup, B2 refers to C1. When filling down, Excel will normally change that reference to C2, C3, and so on. With birth dates in A2:A20 and the shared reference date in C1, enter =DATEDIF(A2,$C$1,"Y") in B2 to keep the reference fixed. The dollar signs make the C1 reference absolute, while A2 remains relative so it advances for each person. You can also select the C1 reference in the formula bar and press F4 on Windows to cycle through reference types; on Mac, use Command+T in many Excel configurations or add the dollar signs directly.

After filling the formula, change a sample birth date temporarily and confirm its age changes as expected, then undo the edit. If the worksheet is used as a formal record, keep the original birth-date data unchanged and document which date the ages were calculated against. A TODAY() result is dynamic: it recalculates when Excel recalculates the workbook, so a saved file opened later can show a different age.

Common Mistakes

Subtracting birth year from the current year without checking the birthday. A formula based only on year numbers can be one year too high before the person’s birthday. DATEDIF with the “Y” unit accounts for whether the anniversary has passed.
Using the wrong DATEDIF unit. The third argument must be in quotation marks. “Y” returns completed years; other units return different measures. For age in whole years, use uppercase Y as shown.
Entering a date as text. A value such as an imported date may look correct but be stored as text, which can cause an error or an incorrect calculation. Confirm Excel recognizes it as a date rather than relying only on its appearance.
Including the heading row in the formula fill. Start the formula beside the first birth date, usually in row 2, not in the heading row. Otherwise the formula may refer to text such as Birth date.
Expecting a decimal age. DATEDIF with “Y” intentionally counts completed years, not a fractional age. A result of 34 means 34 full years have elapsed, not that the person is exactly 34 years old for the entire calendar year.
Forgetting that TODAY() changes. It is right for a live current-age list, but it is not a fixed historical value. Use a reference date in a cell when you need the same age calculation to remain tied to a particular day.

Tips

Format the birth-date column consistently so dates are easy to review. A display such as 15-Aug-1990 can make day, month, and year less ambiguous than a short date whose order depends on regional settings.
Turn the data range into an Excel table with Insert > Table. When you add a new row, Excel can extend the formula column automatically. Confirm the formula is included in the table and that the new birth date is entered as a date.
For a small list, use the fill handle; for a table, let Excel propagate the calculated column. In either case, inspect the first and last results after adding records to catch an accidental gap or mismatched row.
Use a separate reference-date cell for repeatable reporting. You can label it and apply a date format, making the calculation basis visible to colleagues instead of hiding it inside a formula.
If a workbook must preserve a snapshot of ages, copy the results and use Home > Paste > Paste Values. This replaces the formulas with their current results. Keep a copy of the formulas or source data if you may need to update the ages later.
For people born on February 29, consider the organization’s rule for when their birthday is treated as occurring in non-leap years. The function calculates completed calendar years, but policies for legal age, benefits, or eligibility can specify how leap-day birthdays are handled.

Troubleshooting

The result is #NUM!. DATEDIF returns this error when the start date is later than the end date. Check that the birth date is not in the future and that the reference-date cell is correct. If a future date is a possible data-entry mistake, fix the source value rather than treating the error as a valid age.

The result is #VALUE! or an unexpected number. Check whether the birth date or reference date is stored as text. Re-enter the date in a format Excel recognizes, or use Data > Text to Columns to convert imported date text when appropriate. Confirm that the date order is interpreted correctly for your region; 04/05/2000 can mean different days in different settings.

Excel displays the formula instead of an age. The cell may be formatted as Text, or Show Formulas may be turned on. Change the cell format to General, edit the formula (press F2, then Enter on Windows; on Mac, use Control+U to edit the active cell in many versions, or click the formula bar), and check that the formula begins with an equals sign. Toggle Show Formulas from the Formulas tab if formulas are being shown throughout the sheet.

The formula is not updating. TODAY() updates when Excel recalculates. On Windows, press F9 to recalculate; on Mac, use Fn+F9 on keyboards where function keys control hardware features. Also check Formulas > Calculation Options and make sure calculation is set to Automatic if you expect it to update automatically. In Excel for the web, edits and recalculation are generally handled in the browser, but a fixed reference date still gives more stable reporting.

The formula appears to be rejected. Confirm that the quotation marks around Y are straight double quotes and that every opening parenthesis has a matching closing parenthesis. Excel normally uses commas between arguments in the formula shown here, as configured for the stated Windows setup. Regional list-separator settings can differ; if Excel flags commas, check the separator expected by your installation rather than changing the function name.

Frequently Asked Questions

Does DATEDIF calculate age in completed years?
Yes. With the “Y” unit, it returns the number of full years between the birth date and the end date. It does not round a partial year up.

Can I calculate age as of a date other than today?
Yes. Put the date you want to use in a cell and use that cell as DATEDIF’s second argument. For a list, make the reference absolute with dollar signs before filling the formula down.

Why can’t I find DATEDIF in Excel’s function suggestions?
DATEDIF is retained for compatibility with older spreadsheet files and may not appear in autocomplete or the Insert Function list. You can type it directly; its absence from suggestions does not mean the formula is invalid.

Will the age update automatically on a person’s birthday?
When the formula uses TODAY(), the result can update after the workbook recalculates on or after the birthday. A workbook left open may not visibly refresh at the exact moment of midnight, so recalculate or reopen it if an immediate update matters.

Can I show years and months instead of whole years?
Yes, but that is a different result from age in years alone. DATEDIF can calculate completed months with the “YM” unit alongside completed years, but use separate, clearly labeled results if the audience needs both parts rather than one whole-year age.

Does the formula work in Excel for Mac and the web?
Yes, DATEDIF and TODAY() are available in current Excel for Mac and Excel for the web. The formula itself is the same; keyboard shortcuts and some ribbon placement can vary by operating system or version.

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.