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.
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:
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.
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:
Figure 4. Using the IF function