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

Posted on by Shakil Ahmed

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.

Calculate Age on a Specific Date with Formula in Excel

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)

Use Generic Formula with INT Function to Get Age on a Specific Date

  • Press Enter to get the age.

Using Generic Formula with INT Function in Excel Formula to Calculate Age on a Specific Date

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.

Dragging Fill Handle to Copy Excel Formula to Calculate Age on a Specific Date

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

Read More: How to Calculate Age Between Two Dates in Excel

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")

Employ IF Function to Calculate Age on a Specific Date in Excel Formula

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.

Read More: How to Convert Date of Birth to Age in Excel

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)

Age Calculation on a Specific Date with Excel YEARFRAC Function

 

Using YEARFRAC Function in Excel Formula to Calculate Age on a Specific Date

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.

Applying ROUNDDOWN Function to Calculate Age on a Specific Date in Excel Formula

 

Read More: How to Calculate Age in Excel in dd/mm/yyyy

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")

Employ Excel Formula with DATEDIF Function to Calculate Age on a Specific Date 

  • 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"

Determine Age with Years, Months, and Days in Excel

  • Press Enter to get the age.

Using DATEDIF Function in Excel formula to Calculate Age with Years Months and Days on a Specific Date

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.

Read More: How to Calculate Age in Excel in Years and Months

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")

How to Calculate Age from Date of Birth in Excel

  • 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.
  • Drag the Fill Handle to copy the formula.

Using TODAY Function in Excel Formula to Calculate Age from a Specific Date of Birth

  • Below you can see that I have copied the formula to the other cells.

Download Practice Workbook

Here, I have shared the practice workbook. You can download it from the link below.

Related Articles

<< Go Back to Calculate Age | Date-Time in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Leave a Reply

Your email address will not be published. Required fields are marked *

Advanced Excel Exercises with Solutions PDF