How to Calculate Age in Excel (DATEDIF Formula, Years Months Days)

The short answer

With the date of birth in A2, =DATEDIF(A2,TODAY(),"Y") gives the age in completed years. For an age as on a fixed date, replace TODAY() with that date, for example DATE(2026,8,1). The same formula works in Google Sheets. To double-check one person's age in years, months and days, use the Age Calculator and set the Age as of date.

The basic formula: age in years

Put dates of birth in column A, starting at A2. Make sure they are real dates, not text (see troubleshooting below).

In B2, type =DATEDIF(A2,TODAY(),"Y").

Press Enter and fill down.

DATEDIF counts complete years, so someone born on 15 March 1998 is 28 on 26 October 2026, and stays 28 until 15 March 2027.

TODAY() updates every time the file recalculates. If you need ages frozen on a date (for a report or an application), use a fixed date instead.

DATEDIF units

Microsoft's documentation lists these units for the third argument:

Unit	What it returns
"Y"	Complete years in the period
"M"	Complete months in the period
"D"	Days in the period
"YM"	Months left over after complete years
"YD"	Days between the dates, ignoring years
"MD"	Days between the dates, ignoring months and years

Age in years, months and days

Combine "Y" and "YM" for the years and months. For the days, Microsoft says it does not recommend "MD" because it can return a negative number, a zero or an inaccurate result. Its suggested workaround is to calculate the leftover days separately, and a reliable way to do that is with EDATE:

• Years: =DATEDIF(A2,B2,"Y")
• Months: =DATEDIF(A2,B2,"YM")
• Days: =B2-EDATE(A2,DATEDIF(A2,B2,"M"))

Here A2 is the date of birth and B2 is the "as on" date (or TODAY()). The days formula finds the last date the person completed a full month and counts the days since then. Format the days cell as Number, not Date.

To show it all in one cell:

=DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months, "&(B2-EDATE(A2,DATEDIF(A2,B2,"M")))&" days"

Worked examples

Date of birth	As on	Years	Months	Days
15-03-1998	26-10-2026	28	7	11
02-08-2005	01-08-2026	20	11	30

In the second row, the person turns 21 one day after the cut-off date. That one day matters for age limits, which is why exam and job notices state an exact "as on" date. For a full guide to cut-off dates, see How to Calculate Age as on a Cut-off Date for Exams and Jobs.

Age in total months or total days

Some forms ask for age in months (for example for infants) or a count of days:

• Total months: =DATEDIF(A2,B2,"M")
• Total days: =B2-A2 (format the cell as Number)

For a date of birth of 15-03-1998, as on 26-10-2026, that gives 343 months and 10,452 days. Subtracting one date from another is the method Microsoft itself suggests for counting days, and it avoids DATEDIF entirely.

Age as on a specific date

Replace TODAY() with a fixed date. Use the DATE(year, month, day) function so the formula does not depend on how your computer formats dates:

=DATEDIF(A2,DATE(2026,8,1),"Y")

Or put the cut-off date in a cell, say E1, and refer to it with a dollar sign so it stays fixed when you fill down: =DATEDIF(A2,$E$1,"Y").

Checking eligibility against an age limit

Once you have the age, a simple IF flags who qualifies. For an age range of 18 to 27 as on the date in E1:

=IF(AND(DATEDIF(A2,$E$1,"Y")>=18,DATEDIF(A2,$E$1,"Y")<=27),"Eligible","Not eligible")

Always read the notice carefully: some say "not less than 18 and not more than 27 years", others give a range of birth dates instead. A birth-date range is easier to check directly with the dates themselves.

An alternative: YEARFRAC

=INT(YEARFRAC(A2,TODAY(),1)) also gives completed years. YEARFRAC returns the fraction of a year between two dates, the 1 tells it to use actual day counts, and INT drops the decimal part. DATEDIF with "Y" is usually simpler to read, so most people use that.

Troubleshooting

• #NUM! error. The start date is later than the end date. Check that the date of birth is in the first argument.
• #VALUE! error. One of the cells holds text that looks like a date. If dates are left-aligned, they are probably text. Re-enter them, or use Data → Text to Columns and choose the date order (DMY) to convert them.
• Wrong age by a whole year. Day and month may be swapped. 03-04-2000 is 3 April in DD-MM order but 4 March in MM-DD order. Use DATE(year,month,day) to remove doubt.
• The days come out as a date like 11-01-1900. Format the days cell as Number.
• Results look odd for a few rows. Microsoft notes that DATEDIF exists to support older Lotus 1-2-3 workbooks and may give incorrect results in some situations, so test your formula on two or three people whose ages you already know.

When a calculator is quicker

For one person, the Age Calculator is faster than building a sheet. Enter the date of birth and change Age as of to your cut-off date. It shows years, months and days, the total number of days, days until the next birthday and the weekday of birth. For counting days between any two dates, use the Date Difference Calculator.

A spreadsheet is the better choice when you have a list, such as a class register or an applicant list, and need the same cut-off applied to every row. For more on how years, months and days are counted, see How to Calculate Your Exact Age in Years, Months, and Days.