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. Calculate Age from Date of Birth or friends age employee records, student details, or personal tracking DATE Function DATE Function This function returns a specific date and can suitable for fixed reference dates. This function returns a specific date and can suitable for fixed reference dates. returns a specific date TODAY Function TODAY Function This function returns the present day’s date. This function returns the present day’s date. returns the present day’s date MONTH Function MONTH Function This function returns the month in numeric form (1 to 12) from a given date. This function returns the month in numeric form (1 to 12) from a given date. returns the month in numeric form (1 to 12) YEAR Function YEAR Function This function returns the year from the given date. This function returns the year from the given date. returns the year DAY Function DAY Function This function returns the day in numeric form (1 to 31) from a given date. This function returns the day in numeric form (1 to 31) from a given date. returns the day in numeric form (1 to 31) IF Function IF Function This function is used to test a specific given condition. This function is used to test a specific given condition. 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) =YEAR(TODAY()) - YEAR(A1) The above formula will find the number of completed years between the birth date and today’s date 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) =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. 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. 1st formula 2nd formula birthday has not passed value 29 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" =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. general formula cell value 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. C7 displays the final output value 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) =DATEDIF(B6, TODAY(), "Y") =DATEDIF(B6, TODAY(), "Y") =DATEDIF(B6, TODAY(), "Y") =INT(YEARFRAC(B8, TODAY(), 1)) =INT(YEARFRAC(B8, TODAY(), 1)) =INT(YEARFRAC(B8, TODAY(), 1)) =ROUNDDOWN(YEARFRAC(B9, TODAY(), 1), 0) =ROUNDDOWN(YEARFRAC(B9, TODAY(), 1), 0) =ROUNDDOWN(YEARFRAC(B9, TODAY(), 1), 0) =INT(DATEDIF(B10, TODAY(), "Y") + DATEDIF(B10, TODAY(), "YM")/12) =INT(DATEDIF(B10, TODAY(), "Y") + DATEDIF(B10, TODAY(), "YM")/12) =INT(DATEDIF(B10, TODAY(), "Y") + DATEDIF(B10, TODAY(), "YM")/12) =YEAR(TODAY()) - YEAR(B11) - IF(EOMONTH(B11, 0) > TODAY(), 1, 0) =YEAR(TODAY()) - YEAR(B11) - IF(EOMONTH(B11, 0) > TODAY(), 1, 0) =YEAR(TODAY()) - YEAR(B11) - IF(EOMONTH(B11, 0) > TODAY(), 1, 0) That’s it.

Walkthroughs, tutorials, guides, and tips. This story will teach you how to do something new or how to do something better.

Arweave

How to Use Excel TODAY Function? -> Excel 24x7

How to Use Excel DATE Function -> Excel 24x7

View Website Here

Read My Stories

Too Long; Didn't Read

Make resilience your competitive advantage

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

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

Excel Formulas for Assigning Categories by Value Range Using Functions!

Using DATEDIF function to Return Difference Between any Two Dates! -> Excel24x7

4 Data Transformations Made Spreadsheet-Easy

5 Excel Features That Can Grow Your Net Worth in No Time

5 Ways to Add Security to Excel & PDF with .NET Document APIs

Excel Formulas for Assigning Categories by Value Range Using Functions!

Using DATEDIF function to Return Difference Between any Two Dates! -> Excel24x7

4 Data Transformations Made Spreadsheet-Easy

5 Excel Features That Can Grow Your Net Worth in No Time

5 Ways to Add Security to Excel & PDF with .NET Document APIs

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps