How to Calculate Age and Date Differences in Excel

To calculate age from a birthdate in Excel, put the birthdate in cell A2 and type =DATEDIF(A2,TODAY(),"Y") in another cell. Excel shows the person’s age in whole years, and the number goes up by itself on their birthday.

These formulas work in Excel for Microsoft 365, Excel 2024, 2021, 2019 and 2016 on Windows and Mac, and in Excel for the web. They also work in Excel 2013 and 2010, but Microsoft ended support for Office 2013 on April 11, 2023, so those versions no longer get security fixes. The steps follow Microsoft’s DATEDIF function, Calculate age and Calculate the difference between two dates articles.

Excel formulas to calculate age from a birthdate
Illustration: Excel formulas to calculate age from a birthdate.

Before You Start: Make Sure Excel Sees a Real Date

Every formula here depends on the birthdate being stored as a date, not as text that only looks like a date. Excel stores dates as serial numbers that count days from January 1, 1900, which is how it can subtract one date from another. The same minus-sign formulas work on plain numbers too; see how to subtract in Excel.

  • Type birthdates in a format Excel recognizes for your region, such as 3/15/1990 in the United States.
  • To check a cell, type =ISNUMBER(A2) in an empty cell. TRUE means Excel sees a real date. FALSE means the entry is text, and the age formulas will return #VALUE!.
  • A real date usually lines up on the right side of the cell. Text lines up on the left.
  • Excel doesn’t treat dates before January 1, 1900 as dates, so a birthdate like 1895 stays as text and can’t be used in these formulas.

If you type a lot of birthdates, the pop-up calendar for Excel cells helps avoid typos.

How to Calculate Age in Years in Excel

  1. Type the birthdate in a cell, such as A2. Excel moves it to the right side of the cell when it recognizes the date.
  2. Click the cell where you want the age, such as B2.
  3. Type =DATEDIF(A2,TODAY(),"Y") and press Enter. The cell shows the age in complete years, such as 36 for someone born on March 15, 1990, checked on October 17, 2026.
DATEDIF formula in the Excel formula bar returning ages in column B
DATEDIF with TODAY() returns each person’s age in whole years.
  1. To fill in ages for a whole list, click B2, then drag the fill handle (the small square at the bottom-right corner of the cell) down the column. Excel changes A2 to A3, A4 and so on for each row.

If the cell shows a date like 2/5/1900 instead of a number, select it, go to Home > Number Format and choose General.

Steps to calculate age from a birthdate in Excel
Illustration: how to calculate age from a birthdate in Excel.

How the DATEDIF Formula Works

DATEDIF has three parts: DATEDIF(start_date,end_date,unit).

  • start_date is the birthdate (A2).
  • end_date is the date you measure to. TODAY() returns today’s date, so the age recalculates whenever the workbook recalculates.
  • unit tells Excel what to count, in quotation marks.
Unit What it returns Example (3/15/1990 to 10/17/2026)
“Y” Complete years 36
“M” Complete months 439
“D” Total days 13,365
“YM” Months left over after the last full year 7
“YD” Days since the last birthday, ignoring years 216
“MD” Days left over, ignoring months and years Not recommended

Microsoft gives two cautions. DATEDIF exists mainly to support older workbooks from Lotus 1-2-3 and may calculate incorrect results in some situations. Microsoft specifically doesn’t recommend the “MD” unit, which can return a negative number, zero or an inaccurate result. The “Y”, “M” and “YM” units are the ones Microsoft uses in its own age examples.

Excel may not suggest DATEDIF while you type a formula, unlike most functions. Type the full name yourself; the formula still works.

How to Show Age in Years, Months and Days

With the birthdate in A2, build each part in its own cell:

  1. Years: =DATEDIF(A2,TODAY(),"Y")
  2. Remaining months: =DATEDIF(A2,TODAY(),"YM")
  3. Remaining days: =TODAY()-EDATE(A2,DATEDIF(A2,TODAY(),"M")). Format this cell as General.

The days formula avoids the “MD” unit. EDATE moves the birthdate forward by the number of complete months the person has lived, landing on their most recent “month birthday,” and subtracting that from today leaves the extra days. For someone born on March 15, 1990, on October 17, 2026 the three cells show 36, 7 and 2.

To show it all in one cell, join the parts with the & symbol and some text:

=DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months, "&TODAY()-EDATE(A2,DATEDIF(A2,TODAY(),"M"))&" days"
Excel cell showing age in years, months and days
Joining DATEDIF and EDATE results shows the full age in one cell.

The cell reads “36 years, 7 months, 2 days.” Leave off the days part if you only need years and months. The result is text, so you can’t sort or add these cells as numbers. Keep the plain “Y” formula in another column if you need to sort by age. The same & technique is how you combine first and last names in Excel.

Calculate Age on a Specific Date

Replace TODAY() with the date you care about, such as the first day of school or the end of a plan year.

  1. Type the date in a cell, such as E1.
  2. In B2, type =DATEDIF(A2,$E$1,"Y") and press Enter.
  3. Drag the fill handle down. The dollar signs keep every row pointing at E1. Pressing F4 while the cursor is on E1 in the formula adds them for you on Windows.

To put the date in the formula itself, use the DATE function: =DATEDIF(A2,DATE(2026,12,31),"Y") returns the age on December 31, 2026. If the end date is earlier than the birthdate, DATEDIF returns #NUM!.

Other Age Formulas and When to Use Them

Formula What it gives you Watch out for
=YEARFRAC(A2,TODAY(),1) Age with decimals, like 36.59 Leave out the 1 and Excel uses a 30/360 day count meant for finance, which can be slightly off for ages
=INT(YEARFRAC(A2,TODAY(),1)) Whole years using YEARFRAC DATEDIF with “Y” is the simpler choice for whole years
=(YEAR(NOW())-YEAR(A2)) Quick year subtraction from Microsoft’s Calculate age page One year too high until the birthday happens this year
=(TODAY()-A2)/365.25 Approximate age in years Can be off by a day near the birthday
=DAYS(TODAY(),A2) or =TODAY()-A2 Age in days Format the cell as General
=DATEDIF(A2,TODAY(),"M") Age in months, useful for babies Counts only complete months

Microsoft documents the basis options in its YEARFRAC function article: 1 means actual days, and leaving it blank means 30/360.

Find a Next Birthday or a Milestone Age

These formulas answer questions that come up with birthday lists and benefit planning:

  • Next birthday: =EDATE(A2,12*(DATEDIF(A2,TODAY(),"Y")+1)). Format the cell as a date. For a March 15, 1990 birthdate checked on October 17, 2026, it returns 3/15/2027.
  • Days until the next birthday: =EDATE(A2,12*(DATEDIF(A2,TODAY(),"Y")+1))-TODAY() returns 149 in the same example. Format it as General.
  • Date someone turns a certain age: =EDATE(A2,12*65) returns the 65th birthday, March 15, 2055.

EDATE adds whole months, which handles leap-day birthdays: a person born on February 29 gets February 28 in years without a leap day. Microsoft’s EDATE function article has more on the function.

How to Fix Strange Numbers and Errors

  • You see a date instead of an age: format the cell as Number or General. Select the cell and choose Home > Number Format > General.
  • You see #NUM!: DATEDIF returns #NUM! when the start date is later than the end date. Check that the birthdate is the first argument and that nobody typed a birth year in the future.
  • You see #VALUE!: Excel didn’t recognize the birthdate as a date. Use the ISNUMBER check above, then retype the date in a format Excel accepts.
  • You see #NAME?: the function name is misspelled, or the quotation marks around the unit are curly quotes pasted from another app. Retype the formula in Excel.
  • Rows without a birthdate show 126 or an error: a blank cell counts as January 0, 1900. Use =IF(A2="","",DATEDIF(A2,TODAY(),"Y")) so empty rows stay empty.
  • The age is one year off: you are probably using the YEAR subtraction formula. Switch to DATEDIF with “Y”.
  • The age doesn’t change from day to day: the workbook may be set to manual calculation, or the end date was typed in. Pressing Ctrl+; enters today’s date as a fixed value that never updates, while TODAY() updates. To fix that, follow the steps to update an Excel spreadsheet automatically.
  • Dates appear in the wrong order (day before month): check your system date settings, or change the date format in Windows 11.
  • Excel turned something into a date you didn’t want: see why Excel changes numbers to dates.

To confirm a formula is right, test it on your own birthdate or on a date exactly one year ago, which should return 1.

Want to list people from oldest to youngest? The same date rules apply when you sort by date in Excel Online.

Subtract Two Dates to Find Elapsed Days

To count the days between two dates, put the start date in A1 and the end date in B1, then enter =B1-A1 in another cell. Format that cell as General or Number so it shows a count, not a date. For example, October 1, 2026 to October 12, 2026 returns 11. The count includes weekends, and swapping the two dates gives a negative number. For weeks, divide by 7. To fill a column with dates instead of typing each one, see how to auto populate the date in Excel.

To count only workdays, Microsoft uses =NETWORKDAYS.INTL(A1,B1,1), which skips Saturdays and Sundays. Microsoft’s Calculate the difference between two dates article has all of these.

Subtract Years From an Excel Date

To subtract years from a date, put the date in A1 and the number of years in B1, then enter =DATE(YEAR(A1)-B1,MONTH(A1),DAY(A1)). Format the result as a date with Ctrl+1 > Date. For example, October 12, 2026 minus 2 years returns October 12, 2024. Don’t subtract 365 days per year, because that ignores leap years. This adapts Microsoft’s DATE function example.

Leap days need a decision. For February 29, 2024 minus one year, the DATE formula returns March 1, 2023. If you want February 28, 2023 instead, use =EDATE(A1,-12*B1).

Excel Age Calculation Questions

Does the age update automatically?

Yes. Formulas that use TODAY() recalculate based on the current date each time the workbook recalculates, including when you open it. If you need an age frozen on a certain day, use a typed date instead of TODAY().

Why does Microsoft warn about DATEDIF?

DATEDIF exists to support older Lotus 1-2-3 workbooks and may give incorrect results in some cases, especially with the “MD” unit. The “Y”, “M” and “YM” units in this guide match Microsoft’s own age examples, and the days formula above avoids “MD”.

How do I calculate age in months?

Use =DATEDIF(A2,TODAY(),"M") for complete months. It is handy for babies and toddlers.

Can I calculate the age for a whole column at once?

Yes. Enter the formula once, then drag the fill handle down, or double-click the fill handle to fill to the end of the list.

Does this work in Excel for Mac and Excel for the web?

Yes. DATEDIF, YEARFRAC, EDATE and TODAY work the same way in both. On a Mac, open Format Cells with Command+1 instead of Ctrl+1.

Next, try calculating a percentage in Excel to see what share of a list falls in each age group.