How to Calculate Age in Excel From Date of Birth

If a date of birth is stored in cell A2, the simplest Excel formula for calculating the person’s current age in completed years is:

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

The formula compares the date of birth with today’s date and returns the number of complete years between them. Because TODAY() supplies the current date, the result updates when Excel recalculates the workbook.

Quick answer: For a normal current-age calculation, use =DATEDIF(A2,TODAY(),"Y"). Use a fixed reference date instead of TODAY() when you need someone’s age on a particular cutoff, past, or future date.

Calculate Current Age From Date of Birth in Excel

Suppose column A contains dates of birth and you want each person’s current age in column B. If the first date of birth is in A2, enter this formula in B2:

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

Press Enter and Excel returns the person’s age in completed years. For example, someone does not become 32 merely because the current calendar year is 32 years after their birth year. The 32nd birthday must actually have been reached. The "Y" unit handles that completed-year calculation.

How the DATEDIF Age Formula Works

The basic syntax of the function is:

DATEDIF(start_date,end_date,unit)

Part	In an age formula	What it means
`start_date`	`A2`	The date of birth
`end_date`	`TODAY()`	The date on which age is being calculated
`unit`	`"Y"`	Return complete years

Excel stores recognized dates as sequential numerical values. That allows functions such as DATEDIF to compare a birth date with an end date and determine how many complete calendar periods have elapsed.

TODAY() is useful when you want a dynamic current age. If you need age on a fixed date instead, replace TODAY() with that date or with a cell containing the reference date.

A Blank-Cell-Safe Version

Real spreadsheets often contain empty cells, imported text, or dates entered by mistake. A more defensive formula can keep blank rows empty and flag entries that are not usable dates:

=IF(A2="","",IF(NOT(ISNUMBER(A2)),"Check DOB",IF(A2>TODAY(),"Check DOB",DATEDIF(A2,TODAY(),"Y"))))

This version does three checks before calculating age:

If A2 is blank, return a blank result.
If A2 is not stored as a numerical Excel date, return Check DOB.
If the date of birth is later than today, return Check DOB.

This is especially useful for employee lists, student records, membership databases, or other worksheets where dates come from multiple sources.

Which Excel Age Formula Should You Use?

Excel can calculate age in several ways, but the methods do not all represent the same thing. Choose the formula according to the result you actually need.

What you need	Recommended method	Example
Current age in completed years	DATEDIF + TODAY	`=DATEDIF(A2,TODAY(),"Y")`
Age on a specific date	DATEDIF + reference date	`=DATEDIF(A2,B2,"Y")`
Completed years and remaining months	DATEDIF with `"Y"` and `"YM"`	Calculate each component separately
Remaining calendar days after years and months	Shift the DOB by completed months, then subtract	Use DATEDIF + EDATE
Age in complete months	DATEDIF with `"M"`	`=DATEDIF(A2,TODAY(),"M")`
Total days since birth	DATEDIF with `"D"` or date subtraction	`=DATEDIF(A2,TODAY(),"D")`
Fractional or decimal years	YEARFRAC	`=YEARFRAC(A2,TODAY(),1)`
A large list of DOBs	Enter one formula and fill it down	Use AutoFill or an Excel Table

For the ordinary question, “How old is this person?”, completed calendar years are normally the clearest result. Dividing elapsed days by 365 is measuring time differently and should not be treated as an equivalent replacement.

Calculate Age on a Specific Date in Excel

Age does not have to be calculated as of today. Every age calculation has two important dates: the date of birth and the date on which you want to know the age.

You can think of the second date as the reference date or as-of date. TODAY() is simply a dynamic reference date.

Use a Date in Another Cell

If the date of birth is in A2 and the reference date is in B2, use:

=DATEDIF(A2,B2,"Y")

This returns the number of complete years between the two dates.

A fixed reference date is useful when calculating:

age on a school or application cutoff date
age on the date of an event
age at a historical point in time
age on a planned future date
age for an eligibility or reporting date

Lock One Cutoff Date for an Entire List

If many people need to be evaluated against the same cutoff date, place that date in one cell. For example, suppose birth dates are in column A and the common cutoff date is in F2.

Enter this in B2:

=DATEDIF(A2,$F$2,"Y")

The dollar signs make $F$2 an absolute reference. When you copy the formula down, A2 becomes A3, A4, A5, and so on, while the cutoff date remains fixed at F2.

DOB	Cutoff date	Formula
A2	F2	`=DATEDIF(A2,$F$2,"Y")`
A3	F2	`=DATEDIF(A3,$F$2,"Y")`
A4	F2	`=DATEDIF(A4,$F$2,"Y")`

Calculate Age in Years, Months and Days

Sometimes completed years alone are not enough. You may need a result such as 31 years, 5 months and 20 days.

The years and months components are straightforward with DATEDIF. The remaining-days component deserves more care because Excel has a documented limitation with the "MD" unit.

Calculate Complete Years

If the date of birth is in A2 and the as-of date is in B2:

=DATEDIF(A2,B2,"Y")

This returns the number of complete years in the interval.

Calculate Remaining Months

To calculate the number of complete months remaining after the full years have been removed, use:

=DATEDIF(A2,B2,"YM")

For example, a person whose age is 31 years and 5 months would return 31 from the "Y" calculation and 5 from the "YM" calculation.

Calculate Remaining Days Carefully

A calendar-based way to calculate the remaining days is to first determine the completed years and months, move the DOB forward by that many months, and subtract the resulting date from the as-of date.

With the DOB in A2 and as-of date in B2:

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

The logic is:

Find the completed years.
Convert those years to months.
Add the remaining completed months.
Use EDATE to move the birth date forward by that total number of months.
Subtract that shifted date from B2 to obtain the remaining days.

You can combine the three components into one cell:

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

If you want the calculation as of today, replace every B2 reference with TODAY().

Why DATEDIF(…,”MD”) Needs Caution

A commonly published formula uses DATEDIF(A2,B2,"MD") for the final day component. Microsoft currently warns that the "MD" argument has known limitations and can return a negative number, zero, or an inaccurate result in some situations.

Accuracy note: "Y" is useful for completed years and "YM" for remaining months, but do not assume that "MD" is a universally reliable way to calculate the final day remainder.

The EDATE method above makes the remainder logic more explicit by advancing the original date by the number of completed months before calculating the leftover days.

If the spreadsheet is being used for payroll, compliance, legal eligibility, or another policy-controlled process, test the formula against the organization’s required treatment of February 29 and month-end anniversaries before relying on it operationally.

Calculate Age in Months or Days

Age can also be expressed as complete months or total days. These measurements answer different questions from age in completed years.

Age in Complete Months

To return the number of complete months from the date of birth to today:

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

To calculate complete months up to another date, use a reference cell instead:

=DATEDIF(A2,B2,"M")

This is useful for babies and young children, short-term eligibility periods, or records where months are more informative than years.

Total Days Since Birth

To calculate the total number of days between a DOB and today:

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

You can also subtract one valid Excel date from another:

=TODAY()-A2

The result should be formatted as General or Number rather than Date.

Total days lived should not be converted back into calendar age by simply dividing by 365. Calendar years have different lengths, and completed age depends on birthdays rather than an average number of days per year.

Calculate Age Without DATEDIF

DATEDIF is usually the most direct Excel method for completed age, but other functions can be useful when you need a different kind of measurement.

Use YEARFRAC for Fractional Age

YEARFRAC calculates the fraction of a year represented by the days between two dates. If the DOB is in A2, one way to calculate fractional age is:

=YEARFRAC(A2,TODAY(),1)

The third argument is the day-count basis. A basis of 1 uses an Actual/Actual calculation.

If the result were approximately 31.47, it would represent roughly 31.47 years of elapsed time. It is not the same type of result as saying someone is 31 years, 5 months and a certain number of days.

In particular, do not treat the digits after a decimal point as a month number. Six months is roughly half a year, so 24 years 6 months is around 24.5 years, not 24.6 years merely because the month count is six.

YEARFRAC is therefore useful when you actually need a fractional-year measurement. For ordinary completed age, DATEDIF(...,"Y") is easier to interpret.

Why Dividing by 365 Is Only an Approximation

You may see formulas such as:

=INT((TODAY()-A2)/365)

or:

=INT((TODAY()-A2)/365.25)

These formulas convert an elapsed number of days into an estimated number of years. They do not directly count completed birthdays.

The problem is that calendar years do not all contain the same number of days. Leap years contain 366 days, and birthdays near leap-day or other calendar boundaries can expose differences between a day-count approximation and a calendar-based age calculation.

Use a fixed-day divisor only when an approximate elapsed-years measurement is genuinely what you need.

Calculate Age for an Entire Column of Birth Dates

You normally do not need to type the age formula separately for every person. Enter it once, then let Excel apply it to the remaining rows.

Copy the Formula Down

Suppose your worksheet looks like this:

Row	Column A	Column B
1	Date of Birth	Current Age
2	15-Mar-1995	`=DATEDIF(A2,TODAY(),"Y")`
3	22-Nov-1988	Formula copied down
4	09-Jul-2001	Formula copied down
5	03-Feb-1976	Formula copied down

Enter =DATEDIF(A2,TODAY(),"Y") in B2.
Press Enter.
Select B2 again.
Use the fill handle at the lower-right corner of the selected cell.
Drag it down through the remaining rows.

Excel changes A2 to A3, A4, A5, and so on for each row.

If all rows use one fixed cutoff date, lock that cell with an absolute reference such as $F$2 before filling the formula down.

Use an Excel Table for Automatically Filled Formulas

If the data is maintained as an Excel Table, formulas can be easier to manage. When you enter a formula in a table column, Excel can propagate the calculated-column formula through the other rows and apply it to new records added later.

This is useful for employee, member, client, or student records that continue to grow over time. The age calculation remains based on each row’s DOB, while TODAY() supplies the current as-of date.

Common Excel Age Formula Errors and How to Fix Them

Most age-formula problems come from the dates supplied to the formula, the calculation method chosen, or the number format applied to the result.

Problem	Likely cause	What to check
`#NUM!`	The start date is later than the end date	Check the DOB and as-of date
`#VALUE!` or unexpected output	Excel may not recognize the input as a valid date	Check imported or text-formatted dates
Result appears as a calendar date	The result cell uses Date formatting	Change the cell to General or Number
Blank rows produce unwanted output	The formula does not test for blank cells	Wrap the calculation in IF
Future DOB causes an error	The birth date is later than the calculation date	Validate the DOB before calculating
Age is off around a birthday	A day-count approximation may have been used	Use a calendar-based completed-year method
Years and months look right but days look strange	The formula may rely on DATEDIF `"MD"`	Use a safer remaining-days calculation

DATEDIF Does Not Appear in AutoComplete

DATEDIF is an older compatibility function retained by Excel for older workbook support. In some versions or interfaces, it may not behave like newer functions in Formula AutoComplete or the function picker.

You can still type the formula directly:

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

#NUM! Error

DATEDIF returns #NUM! when the start date is later than the end date.

For an age calculation, this often means:

the DOB was entered in the wrong cell
a future DOB was entered by mistake
the cutoff date is earlier than the DOB

Check both dates before changing the formula itself.

#VALUE! or Invalid Date

A value that looks like a date on screen is not necessarily stored as a usable Excel date. This frequently happens with data imported from CSV files, databases, web pages, or systems that use a different date format.

Test whether the DOB cell is numerical with:

=ISNUMBER(A2)

A recognized Excel date normally returns TRUE because Excel stores dates internally as numbers.

If it returns FALSE, clean or convert the source data before depending on the age result.

Excel Shows a Date Instead of an Age

An age formula returns a number. If that number is displayed as something that looks like a date, the output cell probably has a Date number format.

Change the result cell to General or Number. You do not normally need to change the formula.

Blank or Future Date of Birth

For worksheets where blank or invalid DOB entries are possible, validate the source data before running DATEDIF.

A practical version is:

=IF(A2="","",IF(NOT(ISNUMBER(A2)),"Check DOB",IF(A2>TODAY(),"Check DOB",DATEDIF(A2,TODAY(),"Y"))))

This avoids showing an age for an empty row and flags entries that should be reviewed.

Age Is Off Around a Birthday

If a result is one year higher or lower than expected near a birthday, first check which calculation method is being used.

A formula based on:

(TODAY()-A2)/365

is fundamentally different from:

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

The first estimates years from elapsed days. The second asks Excel for complete calendar years. If the intended result is someone’s completed age, use the calendar-based method.

Also verify that the source DOB is correct and that the workbook is recalculating as expected.

Leap-Year and Month-End Birthdays

Leap years and month-end dates are good tests for any age formula because they expose the difference between calendar arithmetic and simple day-count approximations.

A February 29 DOB is a valid Excel date. Problems arise when a calculation needs to decide how that anniversary should be treated in a year that has no February 29, particularly when the result is being used for a rule-based purpose rather than simply reporting an Excel date interval.

For eligibility, payroll, compliance, or legal use: confirm whether the governing rule treats a February 29 anniversary as February 28, March 1, or according to another defined convention. Do not assume that an Excel formula automatically represents the policy or legal rule you need.

Month-end dates can also affect calculations of remaining months and days. For example, January 31 cannot be reproduced as a date in February every year. Functions that move dates by calendar months may therefore use the last valid day of the target month.

This is another reason to distinguish completed calendar years from approximate formulas based on dividing the number of elapsed days by 365 or 365.25.

Frequently Asked Questions

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

DATEDIF is an older compatibility function and may not appear like newer functions in Formula AutoComplete. You can still enter it manually, for example =DATEDIF(A2,TODAY(),"Y").

Will an Excel age formula update automatically?

A formula using TODAY() uses the current date when Excel recalculates the workbook. If the displayed age does not update when expected, check the workbook’s calculation settings.

Can I calculate age when some DOB cells are blank?

Yes. Wrap the age calculation in an IF test so an empty DOB returns an empty result rather than attempting a calculation.

What is the difference between DATEDIF and YEARFRAC?

DATEDIF can return complete calendar years, months, or days between dates. YEARFRAC measures the date interval as a fraction of a year according to a selected day-count basis. For a person’s age in completed years, DATEDIF with "Y" is usually easier to interpret. For a genuinely fractional-year result, YEARFRAC may be more appropriate.

Why does Excel show a date instead of the age?

The result cell is probably formatted as Date. Change its number format to General or Number.

Does Excel handle February 29 birthdays?

Excel recognizes February 29 in leap years and can use it in date calculations. However, the appropriate anniversary in a non-leap year can depend on the rule or policy behind the calculation. For general spreadsheet reporting, test the formula against the behavior you expect. For eligibility or legal decisions, follow the applicable rule rather than assuming an Excel convention.

Is 24.6 years the same as 24 years and 6 months?

No. A decimal represents a fraction of a year. Six months is roughly half a year, so 24 years and 6 months is around 24.5 years, not 24.6 years simply because the month number is six. The exact fractional value can also depend on the day-count method being used.