How to Calculate Age in Excel for a Future Date with DATEDIF

If the date of birth is in A2 and the future date you want to measure age on is in B2, enter =DATEDIF(A2,B2,"Y") in C2. Excel returns the number of whole years completed by that future date. Use real Excel dates in A2 and B2, not text that only looks like a date.

What You’ll Need

Excel for Microsoft 365 on Windows, or Excel for Mac or the web. The steps and DATEDIF formula are essentially the same across these versions.
A date-of-birth value for each person, entered in one column.
A target date in the future, entered in a separate cell. This can be the same target date for everyone in a list.
A blank column for the calculated age. Format the result as a number or leave the General format.

Step-by-Step

Set up the worksheet. Put a clear heading such as Date of Birth in A1 and Future Date in B1. In C1, enter Age on Future Date. Put one person’s date of birth in A2 and the date you want to calculate age on in B2. For a list, use one row per person and keep the same column layout, for example dates of birth in A2:A20 and target dates in B2:B20. If every person has the same target date, you can enter that date in B2 and copy it down, or keep one shared target date in a separate cell as described in the tips below.
Enter actual dates. Select A2, type the birth date, and press Enter. Then enter the target date in B2. Excel may display a date differently from how you typed it, depending on your regional settings; that is fine as long as Excel recognizes it as a date. To check, select the cell and choose Home > Number Format > Short Date. If the display changes to a date format, it is likely stored as a date value. Avoid entering a leading apostrophe, which forces the value to be text.
Choose the result cell. Click C2, the cell beside the two dates. With the date of birth in A2 and the future date in B2, enter =DATEDIF(A2,B2,"Y"). Press Enter. DATEDIF compares the two dates and returns completed years, not a rounded estimate. It does not count a birthday that has not yet occurred by the target date.
Check a concrete example. With A2 set to 15-May-1990 and B2 set to 1-Jan-2030, the formula returns 39. The person turns 40 on 15-May-2030, so they have not completed 40 years by 1 January. If B2 is changed to 15-May-2030, the result becomes 40. If B2 is 14-May-2030, it remains 39. These boundary checks help confirm that the worksheet is measuring age on the specified date rather than simply subtracting birth years.
Copy the formula down for more people. If dates of birth are in A2:A20 and each matching target date is in B2:B20, enter the formula in C2 and use the fill handle at the lower-right corner of C2 to copy it down through C20. Excel adjusts the references for each row: C3 will use A3 and B3, for example. Alternatively, select C2:C20 after entering the first formula and press Ctrl+D on Windows to fill down. In Excel for Mac, use Control+D; if that shortcut is unavailable on your keyboard, use the fill handle.
Use one shared target date when appropriate. If every person should be aged on the same date, put the target date in a fixed cell such as E1. With dates of birth in A2:A20 and the shared target date in E1, enter =DATEDIF(A2,$E$1,"Y") in C2, then fill down. The dollar signs keep the reference on E1 as the formula copies, while A2 changes to A3, A4, and so on. You can label E1 Target Date so other users know what it controls.
Give the result a useful heading and format. Label column C Age (years) or Age on target date. DATEDIF with the “Y” unit returns a whole number, so a General or Number format is appropriate. If you see a date instead of an age, select C2:C20 and choose Home > Number Format > General or Number. A custom heading matters when the workbook may be read later, because an age calculated for a future date is not necessarily the person’s age today.

DATEDIF is a supported Excel function, although it may not appear in the formula autocomplete list. Type its name and arguments directly, then check that the formula has balanced parentheses and straight quotation marks around the unit “Y”. The same formula structure works in Excel for Microsoft 365 on Windows, Mac, and the web; the way you enter dates and the way they display can vary with regional settings.

Common Mistakes

Using TODAY instead of the target date. TODAY returns the current date, so it calculates age today rather than age on a future date. For a planned event, retirement date, eligibility date, or forecast, refer to the cell containing that specific target date.
Subtracting birth years only. A calculation such as target year minus birth year can be one year too high when the birthday has not happened yet in the target year. DATEDIF with “Y” accounts for the month and day as well as the year.
Putting the dates in the wrong order. DATEDIF expects the earlier date first and the later date second. Put date of birth first and the target date second. If the target date is before the birth date, Excel can return the #NUM! error.
Entering dates as text. A value such as ’15/05/1990, with an apostrophe at the start, is text rather than a date. Text dates can also be misread when the workbook’s regional date order differs from the way the date was typed.
Assuming the answer is a decimal age. The “Y” unit gives completed years: 39, not 39.6. That is usually the right interpretation for age, but it is different from dividing the number of days by 365.25.

Tips

Make the target date easy to update. For a cohort or report, store the target date once in a clearly labeled cell such as E1 and use an absolute reference, as in the step above. Update E1 when you need a new as-of date; all the copied age results recalculate automatically.
Show years and months if whole years are not detailed enough. With the birth date in A2 and target date in B2, enter =DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months" in C2. For the example dates 15-May-1990 and 1-Jan-2030, this displays 39 years, 7 months. The result is text, not a number, so use the original whole-year formula in calculations or sorting by numeric age.
Keep original dates intact. Use separate columns for birth date, target date, and result rather than replacing a date with the calculated age. This makes it possible to change the target date and audit how the answer was produced.
Consider your reporting rule for leap-day birthdays. Excel compares actual calendar dates, but organizations or legal rules may define a 29 February birthday differently in a non-leap year. If the result determines eligibility or compliance, confirm the required convention and check examples around the birthday before relying on the worksheet.
Use a consistent date display. A format such as 15-May-1990 can make dates less ambiguous when a workbook is shared between people who use different regional settings. Formatting changes how a date appears; it does not change the underlying date value.

Troubleshooting

The formula returns #NUM!. Check that the target date is on or after the date of birth. DATEDIF cannot calculate a nonnegative age when its start date is later than its end date. Also verify that A2 and B2 contain date values and that the formula arguments have not been reversed.

The formula returns #VALUE! or an unexpected answer. One or both entries may be text, blank, or an invalid date. Select each input cell and change its number format to Short Date as a check, then re-enter the date if it does not behave like a date. For dates imported from another system, use Excel’s data-conversion tools or re-enter the values in a format recognized by your regional settings. Do not assume that changing a text cell’s display format will convert the text into a date.

The age is one year higher or lower than expected. Confirm the exact target day and month, not just the target year. A person whose birthday falls after the target date has not yet reached the next age. Test the date immediately before the birthday and on the birthday itself, as shown in the example. Also confirm that the source date has not been interpreted in month/day order when you intended day/month order, or vice versa.

You see the formula instead of its result. Check whether the cell is formatted as Text. Change it to General, click in the formula bar, and press Enter to re-enter the formula. Also check that Show Formulas is not turned on; on Windows, Ctrl+` toggles formula display. If the keyboard layout makes that key combination awkward, use the Formulas tab and turn off Show Formulas. Mac keyboard layouts can differ, so the ribbon command is a reliable alternative.

The result appears as a calendar date. The formula may be correct while the result cell has a Date format. Select the result cells and choose Home > Number Format > General or Number. A completed age should appear as an integer, such as 39, rather than as a date.

Frequently Asked Questions

Can I calculate someone’s age on a date that is not in the future?
Yes. DATEDIF calculates age between two dates; the target date can be in the past, today, or the future, provided it is not earlier than the birth date. The formula always returns the completed age as of the date in its second argument.

Does DATEDIF round age up?
No. Using the “Y” unit returns completed years and does not round up based on months or days. Someone who is 39 years and 11 months old on the target date is still shown as 39.

Can I calculate age for a whole list using one formula?
Enter the formula in the first result row and copy it down. For dates of birth in A2:A100 and individual target dates in B2:B100, each row uses its own matching pair of dates. If the target date is shared, use an absolute reference such as $E$1 so it does not move when copied.

Why is DATEDIF missing from Excel’s function suggestions?
DATEDIF may not appear in autocomplete or the function list, but you can still type it directly into a cell. Check the spelling, include the opening and closing parentheses, separate arguments with commas, and put the unit in straight quotation marks.

Can I show years and months instead of only years?
Yes. Use the optional years-and-months formula in the Tips section. It returns a readable text label, while the simpler “Y” formula is better when you need a numeric age for calculations, filtering, or analysis.

What should I do about a 29 February birthday?
First decide which rule applies to your purpose, because some policies treat the anniversary in a non-leap year differently. DATEDIF uses calendar dates, but it does not know your organization’s legal or administrative convention. Validate the expected result against the rule you must follow.

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.