How to Calculate Age on a Specific Date with Formula in Excel

Basics of Date Related Functions

There is a function called the TODAY function. Entering TODAY() in any cell will return the date of the current day.
The NOW function returns the current date and time with formatting. To use this, enter NOW() into any cell.

Method 1 – Using INT Function to Calculate Age on a Specific Date in Excel

INT((Calculating Date – Date of Birth) / 365))
[/wpsm_box]

Steps:

Select the cell where you want to calculate the age. Here, I selected cell E5.
Enter the following formula in cell E5:

=INT((D5-C5)/365)

Press Enter to get the age.

How Does the Formula Work?

(D5-C5)/365: Here, the value in cell C5 is subtracted from the value in cell D5. And then the result is divided by 365.
INT((D5-C5)/365): The INT function returns the decimal number as an integer.

Drag the Fill Handle down to copy the formula.

You can see that I have copied the formula to the other cells.

Method 2 – Using the IF Formula to Calculate Age on Specific Date in Excel

Occasionally, someone may erroneously enter a Date of Birth that is more recent than the specific date. To correct that situation, you can use the IF function.

Steps:

Select the cell where you want to calculate age.
Add the following formula into that selected cell and press Enter.

=IF(D5>C5,INT((D5-C5)/365),"Not Born")

How Does the Formula Work?

(D5-C5)/365: The value in cell C5 is subtracted from the value in cell D5, and the result is divided by 365.
INT((D5-C5)/365): The INT function returns the integer portion of the decimal number.
IF(D5>C5,INT((D5-C5)/365),”Not Born”): The IF function checks if the value in cell D5 is greater than the value in cell C5. If the logical test is True, then the formula will return the age. Otherwise, it will return “Not Born”.

Drag the Fill Handle down to copy the formula to the other cells.

The following image shows that the formula has been copied to all the other cells, and the desired output is shown.

Method 3 – Using the YEARFRAC and ROUNDDOWN Functions to Calculate Age on a Specific Date in Excel

The YEARFRAC function requires three parameters: start_date, end_date, and basis. The basis is the day count base. If you use 0 it will count setting the base 360 days in a year. If you use 3, it will count on the base of 365 days in a year. Here I will use 3.

Writing this function may give a fraction value. So I will round this down using the ROUNDDOWN function. This function takes two parameters: The value you want to round and how many decimal places you want. Since I don’t want to see any decimals in the Age value, I will use 0.

Steps:

Select the cell where you want to calculate the age.
Input the following formula into cell E5and press Enter.

=ROUNDDOWN(YEARFRAC(C5,D5,3),0)

How Does the Formula Work?

YEARFRAC(C5,D5,3): Here, the YEARFRAC function returns a decimal value that represents the years between the date in cells C5 and D5.
ROUNDDOWN(YEARFRAC(C5,D5,3),0): The ROUNDDOWN function rounds down the decimal value to zero decimal places.

Drag the Fill Handle down to copy the formula to the other cells.

Method 4 – Using the DATEDIF Formula to Calculate Age on a Specific Date in Excel

This function has three parameters: Start_date, end_date, and format.

Steps:

Select the cell where you want the age. Here I selected cell E5.
In cell E5 enter the following formula.

=DATEDIF(C5,D5,"Y")

Press Enter to get the result.

Here in the DATEDIF function, I selected cell C5 as start_date, D5 as end_date, and “Y” as a unit. “Y” represents all the years during this period. The formula returns the years between these two dates.

Drag the Fill Handle to copy the formula to the other cells.

Method 5 – Using the DATEDIF function and (&) to Calculate Age with Years, Months, and Days on a Specific Date in Excel

The Ampersand Operator (&) is used for joining more than one function together.

Steps:

Select the cell where you want to calculate the age.
Enter the following formula in that selected cell.

=DATEDIF(C5,D5,"Y")&"Y " & DATEDIF(C5,D5,"YM")&"M " &DATEDIF(C5,D5,"MD")&"D"

Press Enter to get the age.

How Does the Formula Work?

DATEDIF(C5,D5,”Y”): The DATEDIF function returns the years between the dates in cells C5 and D5.
DATEDIF(C5,D5,”YM”): The DATEDIF function returns the number of months between the dates in cells C5 and D5 ignoring the days and years.
DATEDIF(C5,D5,”MD”): The DATEDIF function returns the number of days between the dates in cells C5 and D5 ignoring the months and years.
DATEDIF(C5,D5,”Y”)&”Y ” & DATEDIF(C5,D5,”YM”)&”M ” &DATEDIF(C5,D5,”MD”)&”D”: The Ampersand Operator (&) joins the formulas.

Drag the Fill Handle down to copy the formula to the other cells.

In the diagram below you can see that I have copied the formula and got my desired output.

Method 6 – Using the DATEDIF and TODAY Functions to Calculate Age from Date of Birth Only

Steps:

Select the cell where you want to calculate the age. Here I selected cell D5.
In cell D5 enter the following formula.

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

Press Enter to get the age.

How Does the Formula Work?

TODAY(): The TODAY function returns the current date.
DATEDIF(C5,TODAY(),”Y”): The DATEDIF function returns the years between the date in cell C5 and the current date.