How to Calculate Age in Excel from DOB (DATEDIF Formula)

To calculate age in Excel from a date of birth in cell A2, use:

=DATEDIF(A2,TODAY(),"Y")

This returns age in completed years as of today. To get age on a specific date, such as an exam cut-off, replace TODAY() with a cell holding that date or with DATE(2027,8,1). The same formula works in Google Sheets.

If you only need one person's age, the age calculator is faster: enter the date of birth and the "age on" date and it shows years, months and days.

The DATEDIF function

DATEDIF takes three arguments:

=DATEDIF(start_date, end_date, unit)

• start_date: the date of birth
• end_date: the date you want the age on (must be later than the start date, or you get #NUM!)
• unit: what to count, in quotes

Excel keeps DATEDIF for compatibility with older spreadsheets, so it does not appear in the autocomplete list when you start typing. Type the whole name and it works normally.

Age in years from DOB (step by step)

1. Put dates of birth in column A, starting at A2. Make sure Excel sees them as dates, not text (see "Common mistakes" below).
2. In B2 type: =DATEDIF(A2,TODAY(),"Y")
3. Press Enter, then drag the fill handle down to copy the formula for every row.

TODAY() updates every time the file is opened, so the ages stay current. If you need a fixed result for a report, use a fixed date instead.

Age in years, months and days

To show something like "25 years, 4 months, 17 days", join three DATEDIF results with &. With the date of birth in A2 and the "as on" date in B2:

=DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months, "&DATEDIF(A2,B2,"MD")&" days"

Check with an example: born 15 March 2002, age on 1 August 2027.

• "Y" gives 25 (15 March 2002 to 15 March 2027)
• "YM" gives 4 (15 March to 15 July)
• "MD" gives 17 (15 July to 1 August)

Result: 25 years, 4 months, 17 days.

Microsoft's own documentation warns that the "MD" unit can give wrong results in some edge cases, typically around month ends. If you need exact day counts for something official, double-check a few rows with the age calculator.

Age as on a specific date (exam cut-offs)

Exam forms often ask for age as on a fixed date such as 1 August. Put that date in a cell, say E1, and lock it with $ so it does not shift when you copy the formula down:

=DATEDIF(A2,$E$1,"Y")

Or write the date inside the formula:

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

Using DATE(year, month, day) avoids confusion between day-month and month-day formats. For how to read age limits and add category relaxation, see our age calculator for exam age limits guide.

Flag who is eligible

To mark eligibility against an upper limit of 27 on that date:

=IF(DATEDIF(A2,$E$1,"Y")<=27,"Eligible","Over age")

Remember that "must not have attained 28" and "maximum 27" mean the same thing here: completed age of 27 or less.

Other ways to calculate age in Excel

YEARFRAC

=INT(YEARFRAC(A2,TODAY(),1))

YEARFRAC returns the fraction of years between two dates; the 1 uses actual day counts, and INT drops the decimal part to give completed years. It is a handy alternative if you prefer documented functions.

Subtracting dates

Subtracting two dates gives the number of days between them:

=B2-A2

Format the result cell as a number, not a date. Dividing by 365.25 gives an approximate age in years, but it can be off by one around birthdays, so prefer DATEDIF or YEARFRAC for anything that matters.

Calculating age in Google Sheets

Google Sheets supports the same formulas:

• =DATEDIF(A2,TODAY(),"Y") for completed years
• The "years, months, days" formula above works unchanged
• =INT(YEARFRAC(A2,TODAY(),1)) also works

Sheets may use a comma or semicolon as the argument separator depending on your locale setting. If a formula shows a parse error, try semicolons.

Turning ages into percentages

Once you have ages in a column, you might want to know what share of a group is above or below a limit. Count with COUNTIF, then divide:

=COUNTIF(B2:B101,">=18")/COUNTA(B2:B101)

Format the cell as a percentage. For quick one-off percentage sums, the percentage calculator saves building a formula.

Common mistakes

• Dates stored as text. If a date is left-aligned in the cell or the formula returns #VALUE!, Excel is treating it as text. Use Data > Text to Columns, or DATEVALUE, to convert it, or re-enter it in your system's date format.
• Day-month confusion. Indian dates are written day-month-year, but Excel may read 03/04/2002 as 4 March if your system uses US format. Check one known date before trusting a whole column.
• Start date after end date. DATEDIF returns #NUM! if the date of birth is later than the "as on" date, which often signals a typo in the year.
• Forgetting the $ signs. Without them the cut-off date reference shifts as you copy the formula down.
• Using TODAY() in a saved report. The ages change every day. Use a fixed date for anything you will print or submit.
• Dividing days by 365. This ignores leap years and gives wrong ages near birthdays.

DATEDIF covers almost every age question in a spreadsheet. For one person, or to double-check an important result, the age calculator gives the same answer in seconds.

Questions