< 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

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar