Calculate Age Between Two Dates in Excel

To calculate a person’s completed age in years between two dates in Excel, enter the birth date in A2 and the date to calculate age on in B2, then use =DATEDIF(A2,B2,"Y"). For example, a birth date of 15 June 1990 and an end date of 2 November 2026 return 36. You can use a fixed end date for a record or use today’s date for a live age calculation.

The method works in Excel for Microsoft 365 on Windows and uses actual Excel date values, not text that merely looks like a date. The steps below also show how to calculate age on a chosen past or future date, show years and months, and resolve the errors that most often arise.

What You’ll Need

An Excel worksheet with one date for each person’s birth date. This guide uses A2 for the birth date.
A valid end date in B2, such as the date you want to calculate age on. If you want the age as of today, you can use the TODAY function instead.
A result cell for the formula, such as C2. The two dates must be recognized by Excel as dates, and the end date must not be earlier than the birth date.

Step-by-Step

Set up the date columns. Add clear headings such as Birth date, Age as of, and Age in years. Enter each birth date in A2 and the date to calculate age on in B2. Keep one person per row, with that person’s two dates on the same row. If your data is in a formatted Excel table, the same approach works; the cell references in the examples simply need to match your layout.
Enter dates as date values. Type dates in a format Excel recognizes in your region, such as 6/15/1990 in a US locale or 15/6/1990 in many other locales. You can also select a cell and use Home > Number Format to choose a date display. The display format can vary without changing the underlying date. If you are unsure whether Excel recognized the entry, select the cell and inspect the formula bar or change its number format to a date.
Choose a cell for the age result. Click C2, or another empty cell on the same row as the two dates. Keeping the result on the same row makes it easier to copy the calculation down a list and check that each result belongs to the correct person.
Calculate completed years. With the birth date in A2 and the end date in B2, enter =DATEDIF(A2,B2,"Y") in C2 and press Enter. DATEDIF’s Y unit returns the number of full years between the dates; it does not round up based on how close the next birthday is. For the example dates 15 June 1990 and 2 November 2026, the result is 36 because the person has had their 36th birthday but not their 37th.
Use today’s date when you want a live age. If the birth date is in A2 and you want the result to update with the current date, enter =DATEDIF(A2,TODAY(),"Y") in C2. TODAY returns the current date when Excel recalculates the workbook. For a birth date of 15 June 1990, this returns 36 on 2 November 2026. It will change after the next birthday, so it is useful for a current-age list but not for preserving a historical calculation.
Copy the formula to other rows. Select C2 and use the fill handle, the small square at the lower-right corner of the selected cell, to drag the formula down the list. With birth dates in A2:A20 and calculation dates in B2:B20, Excel adjusts the references for each row: the next row will refer to A3 and B3. For a large, consistently filled list, double-clicking the fill handle can fill down to match nearby data. Check the first and last copied rows to confirm the references follow the intended records.
Optionally show years and months. If a more detailed age is useful, use the Y unit for completed years and the YM unit for remaining whole months. With the birth date in A2 and end date in B2, enter =DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months" in C2. For 15 June 1990 through 2 November 2026, this displays 36 years, 4 months. YM counts the remaining whole months after the completed years; it does not show the remaining days.
Keep the end date fixed when the result is for a report. For a report that must always show age on a particular date, enter that date in B2 and use the formula that refers to B2. Do not substitute TODAY if you need the result to remain tied to the report date. You can use the same end date for multiple rows by entering it in B2 and copying the formula down; Excel adjusts the reference unless you make it absolute. For example, with the end date in B2 and the birth date in A2, use =DATEDIF(A2,$B$2,"Y") when copying down so every row keeps using B2.
Check the output and save the workbook. Confirm that the result is a whole number for the years-only formula and that it makes sense beside the dates. If Excel displays a serial number for a date column, apply a date number format; if it displays the age as a date, format the result cell as General or Number. Save the workbook after confirming that sample rows calculate as expected.

Common Mistakes

Reversing the dates: DATEDIF expects the start date first and the later date second. Put the birth date first and the date of calculation second. If the end date is earlier than the birth date, Excel can return #NUM!.
Using the wrong unit: The third argument “Y” means completed years. “YM” means remaining whole months after completed years, not total months. The unit must be in straight double quotation marks.
Assuming a year subtraction gives age: Subtracting the birth year from the current year can be one year too high before the birthday has occurred. DATEDIF accounts for whether a complete year has passed.
Entering dates as text: A date pasted from another system may look right but be stored as text. Excel may then return an error or an unexpected result. Convert or re-enter it as an actual date value before calculating.
Copying a formula with the wrong reference: If the calculation date is in one shared cell, a normal relative reference moves as you fill down. Use an absolute reference such as $B$2 for the shared date. If each row has its own end date, keep the reference relative.
Expecting a stored age to update: A result based on a fixed date stays the same until its input dates change. A result using TODAY can change when Excel recalculates. Choose the method based on whether the answer should be a snapshot or a live value.

Tips

Use a clear “Age as of” heading. A number such as 36 is easier to interpret when readers can see whether it is age today, at the end of a reporting period, or on another date.
Keep source dates in separate columns. Store birth dates and calculation dates as date values, and calculate age in another column. This makes it easier to audit the result and change the as-of date without rebuilding the formula.
Use one consistent date convention. Mixed formats can cause errors when a workbook is shared across regional settings. Use unambiguous dates when entering or importing data, and check a few values after pasting.
Make the calculation date explicit for repeatable reports. TODAY is convenient, but the answer changes over time. A fixed date in a cell gives you a calculation that can be reproduced later, even after the workbook is opened again.
Decide how to treat leap-day birthdays. People born on 29 February may be treated as having a birthday on 28 February or 1 March in non-leap years, depending on the purpose and applicable policy. DATEDIF follows its own date comparison behavior. If the chosen convention matters for legal, eligibility, or benefits decisions, verify the rule that applies and test the formula against dates around the relevant birthday.
Use the same formula in Excel on Mac or the web. The DATEDIF and TODAY formulas shown here work in current Excel for Mac and Excel for the web as well as Microsoft 365 for Windows. Menu labels and some date-entry behavior can vary by platform or regional settings, so check that Excel recognizes each input as a date.

Troubleshooting

If you see #NUM!: Check that the first date is earlier than or equal to the second date. A birth date accidentally entered in the future, or an end date from an earlier year, is a common cause. Also verify the date values themselves; an entry interpreted with month and day reversed can make the order wrong.

If you see #VALUE! or another unexpected result: One of the inputs may be text rather than a date. Select the cell and try applying a date format. If the value does not behave like a date, re-enter it in a format recognized by your regional settings or convert the imported data before using DATEDIF. Also check that the formula uses commas between arguments and straight quotation marks around the unit.

If the formula appears in the cell instead of a result: The cell may be formatted as Text, or Show Formulas may be enabled. Change the cell format to General, re-enter the formula, and check that it begins with an equals sign. If the whole sheet is displaying formulas, use Formulas > Show Formulas to switch that view off.

If the result seems one year too high or low: Compare the end date with the birthday in that calendar year. The years-only result counts completed years, so someone whose next birthday is still ahead has not yet reached that age. Check that the formula uses the intended end date rather than TODAY or a date from a neighboring row.

If copied rows all show the same result: Inspect the formula in a lower row. A reference may have been locked with dollar signs, or the formula may point to the wrong shared date. Use relative references for row-by-row dates and absolute references only for a date that should remain fixed.

Frequently Asked Questions

What is the simplest Excel formula for age between two dates?
With the birth date in A2 and the end date in B2, use =DATEDIF(A2,B2,"Y"). It returns completed years, so it is suitable for a standard age in years.

How do I calculate age as of today?
With the birth date in A2, use =DATEDIF(A2,TODAY(),"Y"). TODAY supplies the current date and the result updates when Excel recalculates. Use a fixed date in a cell instead if you need a lasting report value.

Can Excel show age in years and months?
Yes. With the birth date in A2 and the end date in B2, combine DATEDIF(A2,B2,"Y") with DATEDIF(A2,B2,"YM") to display completed years and the remaining whole months. This format does not include remaining days.

Why does my age formula return #NUM!?
The end date may be earlier than the birth date, or one of the dates may have been interpreted incorrectly. Confirm that both cells contain valid dates and that the birth date is the first DATEDIF argument.

Can I calculate age on a past date?
Yes. Put the past date in B2 and use =DATEDIF(A2,B2,"Y"). The formula calculates completed years as of that date, which is more reliable for historical records than using TODAY.

Does the formula count the current year as a full year?
No. With the “Y” unit, DATEDIF returns only full years completed between the two dates. If the birthday has not yet occurred by the selected end date, that year is not counted.

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.