How to Calculate the Age of a Person in Excel (With Free Age Calculator Template)

Sometimes it is useful to know the age of employees, colleagues, or arbitrary persons. When you have to deal with long lists of calculations, Excel is still the fastest and most efficient way to do it. So, how do you calculate the age of a person in Excel? Here is a straightforward and easy to follow tutorial.

Define important columns

To calculate the age of a person, you need to know the person's date of birth. Therefore, define a column for the person's name and one for its date of birth. Format the cells of the Date of Birth column to a Date format.

Learn to use the DATEDIF() function

The DATEDIF() function calculates the number of years, months, and days between two dates. It needs three parameters:

• The start date of the period you want to calculate.
• The end date.
• The unit measure of the result ("y" – years, "m" – months, or "d" – days).

In order to work well, the start date should be smaller than the end date.

All you have to do to calculate a person's age is to use DATEDIF() function, having parameters the date of birth and today's date. Excel has TODAY() function, which automatically delivers today's date. Thus, the formula to calculate the age of a person is DATEDIF(DATE_OF_BIRTH, TODAY(), "y").

If you want to be more specific than that and have the age with the precision of months, use the formula DATEDIF(DATE_OF_BIRTH, TODAY(), "Y") & "Years," & DATEDIF(DATE_OF_BIRTH, TODAY(), "YM") & "Months instead. This formula can be useful when calculating the age of small children.

Calculate how many days are until the next birthday

Knowing the age of a person is useful, but most of the time, you want to know how many days are until the next birthday. Calculating the next anniversary date helps you plan a surprise party, celebrate the employee of the month, or offer birthday bonuses.

To find out how many days are until the next birthday of a person, we'll use the same DATEDIF() function, but first, we'll calculate when the next birthday will be. We compare the day and month of the date of birth with today's date and month. If these values are smaller, the next birthday will be next year; otherwise, it will be later this year. 

After we find out when the next birthday will be, we compute the remaining days until the next birthday using the formula DATEDIF(TODAY(), DATE_OF_NEXT_BIRTHDAY," d").

Note: We do have a employee birthday calendar template that you can downlad for free.

Download our simple Age Calculator Excel Template

If you don't want to waste time with Excel formulas, download our free Age Calculator Excel template that calculates the age and how many days are until your employees' next birthday. It also includes a list of persons you can easily personalize and filters for quick sorting. Having the right tools saves you time and energy to plan the next birthday party!

Download the Age Calculator Excel Template

Smart managers choose dynamic HR Software - with automatic feeds and notifications

If you want much more than just a static sheet with some formulas, we recommend checking LeaveBoard. You will have access to all the employee birthdays and reports about average age in your organization, and calendar feeds generated automatically with all the birthdays that you can add to Outlook or Google, or get notified in advance with a new email about the future anniversary. If this sounds cool, we can tell you that it is completely free for businesses with less than nine employees.

Note that we updated this report to adress the needs of our customers, so that you can sort by the age so that you can see who are the junior and senior employees, and by the birthday to have an accurate picture about the upcoming birthdays. Additionally from now on, you can access calendar feeds for birthdates within the team, or specific offices, or entire company.

What are you waiting for? Register for an account and bring your team to the platform and boost the efficiency and performance through a web-based HR app.