How to Calculate Age in Excel from Date of Birth

To calculate someone’s age in completed years, put their date of birth in A2 and enter =DATEDIF(A2,TODAY(),"Y") in another cell. Excel returns their age as of today. Before you begin, make sure the birth date is stored as a real Excel date, not text.

What You’ll Need

Microsoft Excel for Microsoft 365 on Windows. The DATEDIF and TODAY functions used here are also available in other current Excel versions, including Excel for Mac and Excel for the web.
A worksheet with a date-of-birth column. For the examples below, each person’s date of birth is in column A, beginning in A2.
An empty column for the calculated age. The examples put the result in B2.

You do not need to install an add-in or enable a special setting. If you are working with private or sensitive birth dates, use a workbook and storage location approved for that information.

Step-by-Step

Enter and check the dates of birth. Put a heading such as Date of Birth in A1, then enter each date below it, starting with A2. Use a clear date format, such as 15-Jun-1990, or the date format appropriate for your region. Excel may display dates differently according to your regional settings, so verify that the day and month have not been swapped. Click a date cell and check the formula bar if you are unsure what value it contains.
Choose the cell for the age. Add a heading such as Age in B1. Select B2, next to the first birth date. Keeping the result in a separate column leaves the original date unchanged and makes it easier to sort or filter the list later.
Enter the age formula. With the date of birth in A2 and the result in B2, enter =DATEDIF(A2,TODAY(),"Y"). Press Enter. DATEDIF compares the start date with the end date, and the “Y” unit asks Excel to return only complete years. TODAY() supplies the current date, so you do not have to update the formula each day.
Check the result against a known example. With a date of birth of 15-Jun-1990 in A2, the formula returns 36 as of 31-Oct-2026. The person has passed their 36th birthday in 2026, so 36 complete years have elapsed. The answer changes as the current date changes; if you open the workbook on a date before 15-Jun-2026, the result would be 35 instead.
Copy the formula down the column. Select B2 and use the fill handle, the small square at its lower-right corner, to drag the formula down alongside the remaining dates. You can also copy B2, select the destination cells, and paste. Excel adjusts the row reference automatically: the next row will refer to A3, then A4, and so on. Check a few results at the top, middle, and bottom of a long list to make sure each age corresponds to the date beside it.
Format the results as whole numbers if needed. Age in completed years is already an integer, but a cell may show decimal places if it has inherited a number format. Select the age cells, then choose Home > Number and use Decrease Decimal until no decimal places show. On Windows, Ctrl+1 opens Format Cells; on Mac, use Command+1. Choose General or Number with zero decimal places. In Excel for the web, use the Home tab’s number-format controls.
Use a fixed date when you need an age as of a particular day. This is useful for a report, eligibility check, or historical record. With dates of birth in A2, the chosen as-of date in B2, and the result in C2, enter =DATEDIF(A2,B2,"Y"). For example, if A2 contains 15-Jun-1990 and B2 contains 31-Oct-2026, C2 returns 36: the 36th birthday has passed by the specified date. Store the as-of date as a real date, and use the same date for every row if the report needs a consistent cutoff.
Optionally keep an age cell blank when its date is missing. For a list where some birth dates have not yet been entered, with the date of birth in A2 and the result in B2, use =IF(A2="","",DATEDIF(A2,TODAY(),"Y")). This checks for an empty A2 before calculating, so Excel does not show an age for a blank row. Copy this version down in place of the basic formula if your list contains gaps. It does not validate nonblank entries, so check that entered dates are genuine and not in the future.

DATEDIF is a long-standing Excel function, although it may not appear in the formula suggestions as you type. Enter the function name and its arguments directly if autocomplete does not offer it. The comma separators shown above are for standard English-language Excel settings; some regional configurations use semicolons as formula separators. If Excel rejects a formula with commas, check the separator settings used by your installation.

Common Mistakes

Subtracting birth year from current year. A calculation such as current year minus birth year is too high for anyone whose birthday has not occurred yet this year. DATEDIF with the “Y” unit accounts for whether the birthday has passed.
Using the wrong date order. A value like 04/05/1990 can mean April 5 or May 4, depending on regional settings. Use an unambiguous entry such as 5-Apr-1990, or confirm the interpreted date in the formula bar before relying on the result.
Using quotation marks incorrectly. The unit argument must be the text “Y”, enclosed in straight double quotation marks. Curly quotation marks copied from formatted text, missing quotes, or a misspelled unit can cause an error.
Including time or text where a date is expected. A pasted value that looks like a date may actually be text, and a cell with an unintended time can complicate checks. Convert or re-enter the value as a valid date before calculating age.
Assuming the result is a decimal age. DATEDIF with “Y” returns completed whole years, not years with a fraction or a count of days. That whole-number age is usually appropriate for birthdays and age eligibility, but may not match a calculation that requires a precise elapsed duration.
Forgetting that TODAY updates. An age calculated with TODAY() can change when the workbook recalculates on a later date. For a report that must preserve an age as of a past cutoff, use a fixed as-of date in a cell instead.

Tips

Keep the original date of birth in its own column. Use a separate age column rather than replacing the dates with calculated values; this preserves the source information and lets you recalculate ages later.
Turn the data range into an Excel table if you regularly add records. Select the data and choose Insert > Table. A formula in a table column normally fills down to new rows, reducing the chance that a new person is left out.
For a workbook intended to be printed or shared as a dated snapshot, enter an as-of date in one clearly labeled cell and use that date for every row. This makes the cutoff visible and prevents different recalculation dates from changing a historical report.
Use a descriptive heading such as Age (years) rather than just Age if readers might confuse completed years with a decimal age or an age in months.
For a large dataset, filter the date-of-birth column for blanks and review future dates before sharing results. A future date is usually a data-entry mistake for a birth-date list, and it can make DATEDIF return an error.

Troubleshooting

Excel displays #VALUE!. The date in A2 may be stored as text or may contain characters that Excel cannot interpret as a date. Select the cell and inspect the formula bar. Re-enter the date using a format recognized by your regional settings, or convert the source data to actual Excel dates. Changing a cell’s display format alone does not convert text into a date.

Excel displays #NUM!. DATEDIF returns this error if the start date is later than the end date. With the basic formula, this often means A2 contains a future date of birth or an incorrectly interpreted date. Check the year, month, and day. With the as-of-date version, also make sure B2 is not earlier than A2.

The formula appears in the cell instead of an age. The cell may be formatted as Text, or the formula may have been entered with an apostrophe before the equals sign. Change the cell format to General, then edit the formula and press Enter again. Also check that Show Formulas is not enabled: on Windows, press Ctrl+` (the key usually above Tab); on Mac, the shortcut is Control+`. You can also turn it off from the Formulas tab’s Show Formulas control.

The answer is one year too high or low. Check whether Excel interpreted the date in the intended order and whether the birthday has passed as of the calculation date. DATEDIF counts completed years, not the difference between the two calendar-year numbers. If you are comparing against an official age on a fixed date, put that exact date in the as-of-date cell and check the source birth date carefully.

Blank rows show an unexpected value. Use the optional IF formula above to keep results blank when A2 is empty. If the cell only looks blank but contains a space or another character, the empty-cell test will not treat it as blank. Clear the unwanted contents or correct the source data, then recalculate.

Frequently Asked Questions

Is DATEDIF available in Excel for Microsoft 365?
Yes. DATEDIF works in Excel for Microsoft 365 on Windows, Mac, and the web, as well as many earlier Excel versions. It may not be listed in the function autocomplete, but you can type it into a formula.

Does TODAY() make the age update automatically?
Yes. TODAY() returns the current date, and Excel recalculates it when the workbook recalculates. The displayed age therefore changes when the person reaches another birthday. If calculation is set to Manual, the value may not update until you recalculate; on Windows, press F9, and on Mac, use Fn+F9 if the function keys are configured for hardware controls.

How do I calculate age on a past or future date?
Put the date of birth in A2, the date to measure age on in B2, and use =DATEDIF(A2,B2,"Y") in C2. The formula returns complete years as of B2, so it can calculate an age for a specific report date without relying on today’s date.

Can Excel show age in years and months?
Yes, but that is a different display from completed years alone. DATEDIF supports the “YM” unit for the months remaining after whole years. If you combine the years and months into a text label, remember that the result is text, not a number suitable for ordinary numeric calculations.

Why does my date look right but still cause an error?
Excel may be displaying text that resembles a date, or it may have interpreted a regional date such as 04/05/1990 in the opposite order. Verify the underlying value and convert it to a true date. Applying a date format changes how a value is shown; by itself, it does not repair text or incorrect date data.

Should I use DATEDIF for legal or official age checks?
It is suitable for calculating completed years from a birth date and a cutoff date, but confirm the applicable rules for the specific program or jurisdiction. An official rule may define the cutoff date or treatment of a birthday differently from a general age calculation.

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.