How to Calculate Age from Date of Birth in Excel? -> Excel24x7!

Multiple Nested Excel Functions Formula to calculate Age from the Birthday!

Calculate Age from Date of Birth: Calculating our own age is quite confusing in most of the times, You can actually use the Excel to calculate the Age from your birthday using essential functions such as DATE, TODAY, YEAR, DAY and MONTH. Combining these functions to form a formula which can help you to calculate your own age or friends age from the DOB itself. You can use the formula to manage your employee records, student details, or personal tracking. Let us get into the tutorial.

DATE Function

• This function returns a specific date and can suitable for fixed reference dates.

TODAY Function

• This function returns the present day’s date.

MONTH Function

• This function returns the month in numeric form (1 to 12) from a given date.

YEAR Function

• This function returns the year from the given date.

DAY Function

• This function returns the day in numeric form (1 to 31) from a given date.

IF Function

• This function is used to test a specific given condition.

By combining these functions, we can accurately calculate a person’s age based on their date of birth.

Method 1: Calculating Age from Birthday using Simple Formula:

The below formula is the basic one to calculate age from date if birth in excel.

=YEAR(TODAY()) - YEAR(A1)

The above formula will find the number of completed years between the birth date and today’s date

Simple formula to calculate Age from birthday using today and year function

As you can see in the above image, the simple formula returned my age from the given date of birth! But the only catch is, the formula returned the total completed years from the birth date and today’s date! To calculate the exact age “Birthdays That Haven’t Occurred Yet in the current year” follow the next formula.

Method 2: Calculate the Age from Birthdays That Haven’t Occurred Yet in the current year:

To accurately determine the age, considering whether the birthday has passed in the current year, use this formula in Cell:

=YEAR(TODAY()) - YEAR(A1) - IF(DATE(YEAR(TODAY()), MONTH(A1), DAY(A1)) > TODAY(), 1, 0)

The above one is the common formula, adjust the cell values to get the exact age from the given birthday.

Calculating Exact Age from the Birthday

From the above image, the second formula gives you the more accurate age rather than the 1st formula. The 2nd formula will determine whether the birthday has passed in the current year or not and the returns the output value. For the given example year, the birthday has not passed and so the formula returns the value 29 as the final age.

Method 3: Formula to Display the Age in Years, Months and Days:

The below formula will returns the age with the detailed result.

=YEAR(TODAY()) - YEAR(A1) & " Years, " & MONTH(TODAY()) - MONTH(A1) & " Months, " & DAY(TODAY()) - DAY(A1) & " Days"

The above one is the general formula, modify the cell value to get the age from the date of birth along with years, months and days.

Formula to display age along with the detailed results.

From the above image, you can see the cell C7 displays the final output value of the formula, the final age has been displayed with the total years, months and days.

Other Combinations of Functions to return Age from Date of Birth:

• =DATEDIF(B6, TODAY(), "Y")

  
  

• =INT(YEARFRAC(B8, TODAY(), 1))

  
  

• =ROUNDDOWN(YEARFRAC(B9, TODAY(), 1), 0)

  
  

• =INT(DATEDIF(B10, TODAY(), "Y") + DATEDIF(B10, TODAY(), "YM")/12)

  
  

• =YEAR(TODAY()) - YEAR(B11) - IF(EOMONTH(B11, 0) > TODAY(), 1, 0)

Other functions to calculate age from formula

That’s it.