Go Back

Get age from birthday

Get age from birthday in Excel

When handling personnel records and dealing with a date of birth, it sometimes becomes necessary to calculate a person’s age or their years of service or enrollment. Here is an easy way to get age from birthday in Excel.

Formula

=INT(YEARFRAC(date,TODAY()))

Explanation

This formula involves three different functions:

  • TODAY: This returns the current date based on your system’s clock
  • YEARFRAC: Calculates the differences between two dates in terms of years in a fraction. It calculates the number of days between two dates and divides them by the number of days in a year to return a fraction. The function requires two arguments;
    • start: We will use the date of birth as our starting point
    • end: We’ll use the TODAY function to get a person’s age as of today
  • INT: Since YEARFRAC returns a decimal, we will use the INT function to convert it into a whole number (integer), which will also round down the value to give us a correct age value

Example: Get age from birthday

Let’s say we have some employee records as follows;

Figure 1. Sample employee data

To calculate age, we will use the following formula:

=INT(YEARFRAC(C5, TODAY()))

We’ll apply this formula to all the rows and get the following result:

Figure 2. How to get age from birthday

 

Let’s go a little further and calculate the number of years each employee has completed in the company.

Figure 3. Calculating years completed

Getting age on a specific date

We used the TODAY function to calculate the current age. But you can also calculate age on a specific date. Simply substitute the TODAY function with a date of your own using the DATE function.

=INT(YEARFRAC(C5,DATE(2016,10,17)))

Bonus tip: using IF

You can use the IF function in Excel to further improve your skills. Let’s suppose you want Excel to identify which employees have served more than 5 years, and are now senior employees. We will use the following formula to achieve this:

=IF(INT(YEARFRAC(D5,TODAY()))>5,"Senior","Junior")

Figure 4. Using the IF function

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

how to change the cell from birthday to age
Solved by S. U. in 22 mins
I want to create a function that will get the age of a certain birthday with this format "1 year 2 months & 3 days"
Solved by A. W. in 28 mins

Leave a Comment

avatar