How to Calculate Age in Excel

If a person’s date of birth is in cell A2, the simplest way to calculate their completed age in Excel is:

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

In this formula, A2 contains the date of birth, TODAY() supplies the current date, and “Y” tells Excel to return the number of completed years. Because TODAY() uses the current date, the calculated age updates when the worksheet recalculates.

Quick Excel Age Formulas

How to Calculate Age from Date of Birth in Excel

For a basic age calculation, you only need the person’s birth date and a result cell.

Step 1: Enter the Date of Birth

Enter the date of birth in a cell, for example A2. Make sure Excel recognizes the entry as a real date rather than plain text.

Step 2: Enter the Formula

In B2, enter:

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

Step 3: Press Enter

Excel returns the person’s age in completed years. This matters because age normally increases when the relevant birthday is reached, not simply when the calendar year changes.

Step 4: Copy the Formula Down

If you have a list of dates of birth, enter the formula once and copy or drag it down the Age column. Excel will adjust A2 to A3, A4, A5, and so on for each row.

How the DATEDIF Age Formula Works

DATEDIF calculates the interval between a starting date and an ending date. Its basic syntax is:

=DATEDIF(start_date,end_date,unit)

For age calculations, the start_date is usually the date of birth. The end_date can be TODAY() or another reference date.

DATEDIF Units: Y, M, D, YM, YD and MD

Why DATEDIF May Not Appear in Excel Autocomplete

DATEDIF behaves differently from many modern Excel functions. Microsoft retains it primarily for compatibility with older Lotus 1-2-3 workbooks. As a result, Excel may not provide the same autocomplete or argument guidance you see with functions such as SUM or TODAY.

You can still type a valid DATEDIF formula directly into the formula bar. See Microsoft’s DATEDIF documentation for its supported units and known limitations.

Calculate Age in Years, Months, and Days

Age can be expressed in several ways depending on whether you need completed years, total months, total days, or a detailed calendar breakdown.

Age in Completed Years

Use:

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

This is the most useful formula when you simply need someone’s current age in whole, completed years.

Age in Total Months

Use:

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

This returns the total number of complete months between the date of birth and today. It does not return only the months left over after the completed years.

Age in Total Days

Because Excel stores dates as sequential numbers, you can subtract one valid Excel date from another:

=TODAY()-A2

Format the result cell as General or Number if Excel displays the result as a date.

Age in Years and Months

To display completed years followed by remaining months, use:

=DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months"

For example, the result could appear as:

31 years, 5 months

Age in Years, Months, and Days

A commonly used Excel formula is:

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

This produces a readable age such as:

31 years, 5 months, 19 days

Important Limitation of the MD Argument

Do not assume that DATEDIF with “MD” is reliable for every date combination. Microsoft documents known limitations with this argument and notes that it can return a negative, zero, or inaccurate result in some circumstances.

If the remaining number of days is important for a report, eligibility decision, legal calculation, or another precision-sensitive use, test the relevant date combinations rather than relying blindly on the MD result.

One way to calculate remaining days after the completed years and months without directly using the MD unit is to create an anniversary anchor date and subtract it from the end date. If the date of birth is in A2 and the reference date is in B2:

=B2-EDATE(A2,12*DATEDIF(A2,B2,"Y")+DATEDIF(A2,B2,"YM"))

Calendar edge cases, especially February 29 birthdays, should still be checked against the age rule you intend to apply.

Calculate Age on a Specific Date in Excel

You do not have to calculate age relative to today. Replace TODAY() with another date when you need someone’s age at a particular point in time.

Using a Reference Date in Another Cell

If A2 contains the date of birth and B2 contains the date on which you want to know the person’s age, use:

=DATEDIF(A2,B2,"Y")

This is useful for questions such as:

• How old was someone on an application date?
• What was their age on a historical date?
• How old will they be at graduation?
• What will their completed age be on a future eligibility date?

If you only need the result rather than an Excel formula, you can use our Age on Specific Date Calculator.

Using the DATE Function

You can also place the target date directly inside the formula with DATE:

=DATEDIF(A2,DATE(2026,9,3),"Y")

Using DATE(year,month,day) avoids ambiguity inside the formula because the order of its arguments is explicit.

Decimal Age on a Specific Date

For a fractional age rather than completed years:

=YEARFRAC(A2,B2,1)

This might return a result such as 31.47 rather than simply 31.

TODAY() vs a Fixed Date

When to Use TODAY()

Use TODAY() when the spreadsheet should always show current age:

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

TODAY() returns the computer’s current date when Excel recalculates the worksheet. This makes the result dynamic rather than permanently fixed.

When to Use a Fixed Reporting Date

A fixed reference date is usually better when you need a reproducible result for a report, audit, archived dataset, application deadline, or historical analysis.

For example, if B1 contains the reporting date and A2 contains the DOB:

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

The dollar signs make B1 an absolute cell reference, so the reporting date stays fixed when the formula is copied down.

Calculate Decimal Age Using YEARFRAC

YEARFRAC Formula

YEARFRAC calculates the fraction of a year between two dates. For a date of birth in A2:

=YEARFRAC(A2,TODAY(),1)

If you want fewer decimal places, you can round it:

=ROUND(YEARFRAC(A2,TODAY(),1),2)

Understanding the Basis Argument

YEARFRAC uses an optional basis argument to determine how days are counted. A basis of 1 means Actual/Actual, so the calculation uses actual calendar days rather than a 360-day financial convention.

Microsoft also recommends entering dates as proper Excel date values or creating them with DATE rather than relying on ambiguous text dates. See the Microsoft YEARFRAC documentation.

Decimal Age vs Completed Age

Decimal age and completed birthday age answer different questions.

For normal birthday-based age in completed whole years, DATEDIF with "Y" is usually easier to interpret. YEARFRAC is more appropriate when the fractional part of the year is actually useful.

Calculate Age Without DATEDIF

Why Simple YEAR Subtraction Can Be Wrong

You may see this formula:

=YEAR(TODAY())-YEAR(A2)

It only compares calendar years. It does not check whether the person’s birthday has already happened this year.

For example, suppose the DOB is 20-Dec-2000 and the reference date is 3-Sep-2026. Subtracting the years gives 26, but the person’s 26th birthday has not happened yet. Their completed age is still 25.

Birthday-Aware Alternative

For ordinary birthdays, you can subtract an additional year when the birthday for the current year has not yet been reached:

=YEAR(TODAY())-YEAR(A2)-IF(TODAY()<DATE(YEAR(TODAY()),MONTH(A2),DAY(A2)),1,0)

This checks the month and day of the birthday rather than subtracting the years blindly.

For a February 29 date of birth, define how your application treats the birthday in a non-leap year before choosing a formula. Excel’s DATE function can roll an unavailable February 29 into March, while some policies may use February 28 instead.

Calculate Age for Multiple People in Excel

Excel is particularly useful when you need to calculate age for an employee list, student register, membership database, research dataset, or another group of records.

Copy the Formula Down

Enter the formula in the first row of the Age column, then use Excel’s fill handle or copy-and-paste to apply it to the remaining rows.

Use a Fixed Reference Date

For a report that must show everyone’s age on the same date, store that reporting date in one cell instead of using TODAY().

Use an Absolute Cell Reference

If the reporting date is in E1 and the DOB is in B2:

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

When copied down, B2 changes to B3, B4, and so on, while $E$1 remains fixed.

Common Excel Age Formula Errors

#NUM!

DATEDIF returns #NUM! when its start date is later than its end date. For an age calculation, check that the DOB comes before the target date.

#VALUE! or Invalid Dates

Excel needs to recognize the DOB and reference date as valid dates. Data imported from another system may look like a date while actually being stored as text.

If necessary, convert text dates to real Excel dates before calculating age.

The Result Appears as a Date

Excel dates are stored as numbers, so cell formatting can change how a numeric result appears. If a number of days or another numeric age result displays as a calendar date, change the result cell’s format to General or Number.

DATEDIF Doesn’t Appear in Autocomplete

This does not necessarily mean the function is unavailable. DATEDIF is a compatibility function and may not receive the same formula assistance as newer Excel functions. Type the complete formula manually.

The Age Is One Year Too High

This commonly happens when the formula calculates only:

current year - birth year

If this year’s birthday has not happened yet, that approach overstates completed age by one year.

DD/MM/YYYY vs MM/DD/YYYY

A date such as 03/04/2000 can be interpreted differently depending on regional settings. It could mean March 4 or April 3.

When writing a date directly into a formula, using DATE can remove that ambiguity:

=DATE(2000,4,3)

Here, the order is explicitly year, month, day.

Also note that some regional versions of Excel use semicolons rather than commas between formula arguments. If a correctly written formula produces a syntax error, check the list separator used by your Excel installation.

How Excel Handles Leap-Year Birthdays

A person born on February 29 creates an edge case because most years do not contain that date.

Calendar-aware Excel functions are preferable to approximations such as dividing elapsed days by 365 because leap years and month lengths vary. However, the correct birthday convention for February 29 can depend on the rule or purpose of the calculation.

For example, one system may treat February 28 as the relevant anniversary in a non-leap year, while another may effectively use March 1. If the distinction matters for eligibility, legal status, or another formal decision, define the rule first and make the spreadsheet follow it consistently.

For a fuller explanation, see how age is calculated for leap-year birthdays.

Which Excel Age Formula Should You Use?

Worked Excel Age Formula Examples

The second example shows why simply subtracting the birth year from the reference year is not enough. The calendar years differ by 26, but the person’s December birthday has not yet occurred.

Excel Age Formula Cheat Sheet

Excel vs an Online Age Calculator

For a quick calculation without creating a spreadsheet, use our Age Calculator.

Frequently Asked Questions

What is the best Excel formula for age?

For ordinary age in completed whole years, a simple choice is =DATEDIF(A2,TODAY(),"Y"), where A2 contains the date of birth. Use another reference date instead of TODAY() if the age must be calculated as of a specific date.

Why doesn’t DATEDIF appear when I type it in Excel?

DATEDIF is an older compatibility function retained by Excel for older workbooks. It may not receive normal autocomplete or argument assistance, but you can still type a valid DATEDIF formula manually.

Does TODAY() update age automatically?

TODAY() returns the current date when Excel recalculates the worksheet. If it does not update as expected, check that workbook calculation is set to Automatic. Microsoft’s TODAY documentation explains the recalculation behavior.

Why does my DATEDIF formula return #NUM!?

One common cause is that the start date is later than the end date. In an age calculation, make sure the date of birth occurs before the current or reference date.

Can I calculate age without DATEDIF?

Yes. YEAR, MONTH, DAY, DATE and logical functions can be combined to check whether the birthday has happened during the reference year. Avoid using only YEAR(end)-YEAR(DOB) because it can overstate age before the birthday.

Is YEARFRAC better than DATEDIF for calculating age?

They serve different purposes. DATEDIF with "Y" is easy to interpret when you need completed years. YEARFRAC is more useful when you need a fractional result such as 31.47 years.

Why does Excel show a date instead of the age result?

The result cell may be formatted as a Date. Change its number format to General or Number when the formula is supposed to return a numeric age, number of months, or number of days.

Does Excel handle February 29 birthdays?

Excel can calculate with February 29 dates, but age on February 28 or March 1 of a non-leap year can depend on the convention your calculation is meant to follow. For important eligibility or legal uses, define that convention explicitly rather than assuming one formula represents every rule.

Related Age Calculation Guides

• How to Calculate Age
• Age Calculator Formula Explained
• How to Calculate Age in Google Sheets
• How Age Is Calculated for Leap-Year Birthdays
• Chronological Age Explained