To calculate someone’s age in completed years in Excel, enter their date of birth in A2 and use =DATEDIF(A2,TODAY(),"Y") in another cell. DATEDIF compares the start date with a later date; with TODAY(), the result updates when the workbook recalculates. You can also use it to show completed months or a years-months-days breakdown.
Before you start, make sure Excel recognises the birth date as a real date, not text. The steps below are for Excel for Microsoft 365 on Windows. The DATEDIF formulas also work in current Excel for Mac and Excel for the web, though some ribbon labels and date-entry behaviour can differ slightly.
What You’ll Need
- An Excel workbook with a date of birth for each person you want to calculate an age for.
- A blank column or cell for the age result. The examples use A2 for the birth date and B2 for the result.
- A reference date if you need an age on a particular day instead of today. You can enter that date in another cell, such as C2.
Step-by-Step
-
Enter a date of birth. Put the person’s date of birth in A2. Enter it in a date format Excel recognises, such as 6/15/1990 in a US date setup or 15/6/1990 in a day/month/year setup. If you are unsure, select the cell and choose Home > Number Format, then select Short Date. The displayed format may change, but the underlying date should remain the same.
-
Choose where the age will appear. Select B2, or another empty cell beside the birth date. Keeping the result in a separate column makes the calculation easier to check and copy down for a list of people.
-
Enter the formula for age in completed years. With the date of birth in A2 and today as the reference date, enter
=DATEDIF(A2,TODAY(),"Y")in B2 and press Enter. The unit argument “Y” tells DATEDIF to return completed whole years, rather than a decimal fraction of a year. For a birth date of June 15, 1990, and a current date of November 3, 2026, the result is 36. -
Check the result against the birthday. DATEDIF counts completed years, so someone whose 36th birthday has not yet occurred in the reference year is still 35. For example, a person born on December 20, 1990 is 35 on November 3, 2026, and becomes 36 on December 20. This is why dividing the number of days by 365 can give the wrong age.
-
Calculate age on a specific date if needed. Enter the date you want to measure age on in C2. With the birth date in A2 and the chosen reference date in C2, use
=DATEDIF(A2,C2,"Y")in B2. For a fixed result, use a date value in C2 rather than TODAY(); this is useful for an application cutoff date, a past event, or an age report that should not change tomorrow. -
Show years and completed months. To find the number of full months between the two dates, use unit “M”. With the birth date in A2 and today as the reference, enter
=DATEDIF(A2,TODAY(),"M"). For the June 15, 1990 example on November 3, 2026, the result is 436 completed months. To show only the months left after counting whole years, use=DATEDIF(A2,TODAY(),"YM"); the same example returns 4. “YM” is a remainder of months, not the person’s total months of age. -
Build a years, months, and days display. If you want one readable result, with the birth date in A2 and today as the reference date, enter
=DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months, "&(TODAY()-EDATE(A2,DATEDIF(A2,TODAY(),"M")))&" days". For June 15, 1990, on November 3, 2026, the result is “36 years, 4 months, 19 days.” This calculates the remaining days after the last completed month using EDATE, rather than relying on DATEDIF’s “MD” unit, which can produce unexpected results for some month-end dates. If you prefer separate columns, use the “Y” and “YM” formulas above and calculate the remaining days in its own cell. -
Fill the formula down for more people. If each birth date is in column A, select B2 and drag the small fill handle at the lower-right corner of the cell down the list. Excel adjusts A2 to A3, A4, and so on. Check a few rows, especially dates near birthdays, before using the results in a report.
Common Mistakes
Using the wrong unit. DATEDIF’s third argument controls the answer: “Y” is completed years, “M” is total completed months, and “D” is total days. “YM” returns leftover months after whole years. Using “M” when you wanted the age in years can return a surprisingly large number, such as 436 rather than 36.
Putting the dates in reverse order. DATEDIF expects the start date first and the end date second. A birth date should normally come before the reference date. Reversing them can produce a #NUM! error.
Assuming a date-looking entry is a date. Imported data may contain text that merely looks like a date. A formula may return #VALUE! or behave inconsistently if Excel cannot interpret the entry as a date. Confirm the cell is a real date before troubleshooting the formula.
Using a 365-day division. A formula that divides the days between dates by 365 ignores leap years and whether the birthday has passed this year. DATEDIF with “Y” is the straightforward choice for completed age in years.
Expecting DATEDIF to appear in formula suggestions. DATEDIF is supported in Excel, but it may not appear in the function autocomplete list. Type the function name and its arguments yourself, checking the commas, quotation marks, and closing parenthesis.
Tips
Use TODAY() when you want a live age that updates over time. It is a volatile function, so Excel may recalculate it when the workbook recalculates. For an official snapshot or a historical report, store the relevant reference date in a cell and refer to that cell instead. That makes the result reproducible and easier for another person to audit.
Format the birth-date column consistently. Select the dates and use Home > Number Format > Short Date, or choose Long Date if spelling out the month helps prevent day/month confusion. Formatting changes how a date displays; it does not repair a value stored as text.
For an age list, put a clear heading such as Age (years) above the result column. When copying formulas, avoid replacing a date cell with a label or blank unless you also handle missing entries. You can filter or sort the finished age column like other numeric results, provided the cells contain numbers rather than text.
Excel may use semicolons instead of commas in formulas when required by regional settings. If Excel rejects a formula with commas, check your list separator settings and try the separator used by other formulas in your workbook. Keep the straight double quotation marks around unit codes, and do not add spaces inside the function name.
Troubleshooting
DATEDIF returns #NUM!. Check that the birth date is earlier than or equal to the reference date and that both are valid dates. If you are using a cell for the reference date, inspect it as well; a date entered for the wrong year can make the start date later than the end date.
DATEDIF returns #VALUE!. One or both date arguments may be text or blank in a way Excel cannot use. Select the date cell and look at the formula bar, then try entering the date again in a recognised format. For data imported from another system, use Text to Columns or a suitable date conversion process to convert text values into Excel dates; confirm the converted dates before relying on the ages.
The answer looks like a date or has decimals. Select the result cell and choose Home > Number Format > General or Number. The “Y” unit should return a whole number of years. If you see a date-like display, the formula may be fine but the result cell may have inherited a date format.
The displayed age is not updating. TODAY() changes when Excel recalculates. On Windows, press F9 to recalculate; if calculation is set to manual, go to Formulas > Calculation Options > Automatic. On Mac, use Formulas > Calculation Options if available in your Excel version, or press Fn+F9 if your keyboard uses function keys for system controls. Excel for the web recalculates formulas as the workbook updates, though a refresh may help if the page is stale.
The result is off by one year. Check the exact birth date, the reference date, and whether the birthday has passed on that reference date. Also verify regional date order: 4/5/2000 can mean April 5 or May 4 depending on settings. Use a clearly spelled month or inspect the underlying date before changing the formula.
Frequently Asked Questions
Is DATEDIF still available in Microsoft Excel?
Yes. DATEDIF works in current Excel for Microsoft 365, even though it may not appear in autocomplete or in the function list. Enter it directly in a formula.
Does DATEDIF calculate age as of today automatically?
Yes, when TODAY() is its end-date argument. The result updates as the current date changes and Excel recalculates. Use a reference-date cell instead when you need a stable or historical age.
Can DATEDIF calculate age in months?
Yes. Use the “M” unit for total completed months, or “YM” for the remaining months after complete years. These are different measures, so choose the one that matches your report.
How do I show the person’s age in years, months, and days?
Use the combined formula in Step 7, which joins completed years and leftover months with days since the last completed month. For month-end dates, the EDATE-based day calculation is preferable to relying on DATEDIF’s “MD” unit.
Can I calculate age on a past date?
Yes. Put the past date in a cell and use it as DATEDIF’s second argument instead of TODAY(). The first argument remains the date of birth, and it must be earlier than or equal to that reference date.
Will the same formulas work in Excel for Mac or the web?
DATEDIF, TODAY, and EDATE are available in current Excel for Mac and Excel for the web. Menu placement and keyboard shortcuts can differ, but the formulas themselves are the same. Regional settings may affect whether your formulas use commas or semicolons.
Related
- Subscript in Google Sheets: Methods That Work
- Subtract Dates in Excel in Years: Two Reliable Methods
- Excel Strikethrough Shortcut: How to Cross Out Text
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel FILTER Formula for Multiple CriteriaTo filter rows in Excel by multiple criteria, use FILTER with a condition for each test: multiply the…
- Excel VLOOKUP IFERROR: Return a Blank for Lookup ErrorsTo make an Excel VLOOKUP display a blank when it returns an error, wrap it in IFERROR: =IFERROR(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"").…
- Excel Find and Remove Text: Replace, Formulas, and TipsTo find and remove text in Excel, press Ctrl+H, enter the text in Find what, leave Replace with…
- Sort an Entire Excel Spreadsheet by One ColumnTo sort an entire Excel spreadsheet by one column, click a cell in your data, choose Data >…
- How to Create Round Labels in Word: A Step-by-Step GuideTo create round labels in Word, set up a label sheet using the stock’s template or measurements, place…
- Excel Formula List: Basic Functions, Uses, and ExamplesA basic Excel formula list includes SUM for totals, AVERAGE for means, COUNT and COUNTA for counting, MIN…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.