Can Excel Calculate Age?

Excel Tips
Updated on January 26, 2024

Microsoft Excel contains a lot of functions. These functions are supported by formulas and the total number of these functions equals to more than 450. Without question, Microsoft Excel is the most popular spreadsheet program in the entire world. The finance sector, people who are trying to establish their own business and small, medium and large-sized companies, all use Microsoft Excel one way or another.

Here’s quick tutorial first to calculate age in Excel:

Not only the employees who are in charge of their businesses but also ordinary citizens use Excel and its more than 450 functions in their personal and private lives as well. Microsoft Excel is used for such processes as managing, organizing and perhaps more importantly calculating data.

From basic calculations to detailed and complicated equations, Microsoft Excel has the capacity and the ability to calculate them all. It is built and designed to handle different types of calculations. The biggest advantage of doing your calculations in Microsoft Excel is the help of its functions, formulas and macros.

Microsoft Excel’s reach and catalogue of functions are so comprehensive that you can pretty much calculate anything you can think of and imagine. Also, sometimes, you can calculate some of the things you cannot even imagine! And (hopefully) one of these calculations you can imagine and want to constantly quantify is age.

No matter if it is yours, one of your family member’s or a historical figure, calculating someone’s age is an action we take all the time. And yes, it is a simple and basic mathematical operation. Therefore, you have many different ways and methods to calculate someone’s age with

Pen and paper,
Calculator,
Calculator application on your phone or on your computer.

And perhaps, the most used method is with your brain!

Every educated adult can calculate one or two people’s age without writing it down. However, how can you calculate a large number of people’s age, store that data and keep it updated on its own? The answer is: With a Microsoft Excel spreadsheet.

And in this blog post, you can find how exactly you can calculate someone’s age and let it update itself without you even doing anything! Let’s look at the functions which help you calculate age in Microsoft Excel.

Different Functions to Calculate Age in Microsoft Excel

Microsoft Excel offers different functions for you in order to calculate any age. These formulas differ from simple ones which you have to operate in manually to detailed ones which do everything for you. So, let’s start with the most basic formula and go from there to the more detailed ones:

The Basic Formula

The most basic formula for calculating the age is manually doing the mathematical operation. For example, you want to calculate someone’s age who was born in 1975. Now, given the fact that the year is 2020 at the moment, the basic subtraction you need to do is:

2020 – 1975 = 45

After putting the data of the current year (your end date) and the person’s birthdate (your start date), Microsoft Excel will give you the answer of 45. This method of calculating age is beneficial in order to quickly subtract a date from another date. Also, it will store all the dates and the calculated age for you. However, it is so simple of a process that you can do this manual process everywhere, even with a pen and paper! It can still be used while calculating age in Microsoft Excel but it is not the preferred formula method for this process.

The YEARFRAC Function

This function is perhaps the most used method of calculating age in Microsoft Excel. The feature it differs from the other functions is that the YEARFRAC function shows the calculated age with a decimal value. It gives you the exact result of a person’s age with fractional years as well.

By calculating the exact number of the days between two dates, it gives a result with decimal figures. The YEARFRAC function’s formula is given below:

=YEARFRAC(start_date, end_date, [basis])

Let’s detail every element of the formula one by one. The “start_date” element is the date of birth of the person. The “end_date” element of the formula is the date you put in in order to calculate that person’s age. By using the TODAY function, you can more easily choose today’s date and calculate that person’s age. Both of these elements of data are required in order to compute. However, the “basis” element is optional.

The basis argument provides the option of how days are counted when calculating the exact number of days between the two dates. The five different options are listed below:

If you put 0 in between the basis brackets, the months are considered as 30 days. By doing that it calculates every year between the two dates as 360 days. This option is designed for the United States’ rules and conventions. And it is also the default option if you leave the basis brackets empty and do not put in any value.

If you put the value of 1 in the basis brackets, all of the months are calculated as they are. February is 28 and 29 days according to the year that it is calculated. All of the months that have 31 days in it are also quantified accordingly. And the leap years are considered due to the fact that they change the number of days in a year.

If you put 2 in the basis brackets, all of the 12 months will be considered as they are 30 days. Therefore, the year in total will be calculated as it always has 360 days for each year.

If you put the value of 3 in between the basis brackets, all of the months will be calculated as how many days they actually have. February will be always considered as 28 days and leap years will not be considered. Therefore, each year between the two dates will be calculated as they are 365 days.

If you put 4 in the basis brackets, all of the 12 months will be considered as they have 30 days. The year will follow as it is 360 days each year. However, this option will follow the European rules and conventions in terms of calendar dates.

For example, if you want to calculate the age of someone who was born in the 22^nd of May in 1975, you put in 22.05.1975 for your start date. And for the end date, by using the TODAY function, you will be putting the day you are doing the calculation. (For September 2020) The answer would be or close to 45.32055, if you put 3 as your basis. However, if you put 0 (the default) in between the basis brackets, it will give you the result of 45.28333.

As it is mentioned above in this chapter, the YEARFRAC function can be used with the TODAY function. The other function it is coupled with is the INT function.

The INT(YEARFRAC) Function

Due to the fact that the YEARFRAC function gives you a result with a fraction or a decimal, you sometimes might need to round that number to an actual figure. The INT(YEARFRAC) function will turn your fractional number into an integer. For example, let’s take the result given in the previous paragraphs.

If you want to round up the age of 45.32055 into an integer, you use the INT(YEARFRAC) formula and get 45 as your result. It is that simple.

The DATEDIF Function

The ‘weird’ thing about this function is that Microsoft Excel does not automatically fill out the formula for you. You have to have the whole syntax and the order of elements accurately in order to get an accurate result as well.

The formula is:

=DATEDIF(start_date, end_date, unit)

The “start_date” and the “end_date” parameters are the same as the ones mentioned above in the other functions. However, the “unit” element offers you the option to choose how the difference between the dates is shown. The options in the unit element are:

Y = This shows the number of years between the two dates.

M = This shows the number of months between the two dates.

D = This shows the number of days between the two dates.

YM = This shows the number of months between the dates while it does not acknowledge the year and the day data.

MD = This shows the number of days while it does not take into account the number of years and months.

YD = This, again, shows the number of days; however, it only takes into account months with it.

If you want to calculate merely someone’s age in years, the best option (which is also the most common one) is the Y unit. This DATEDIF function and its unit options will come in handy when you want to get the number of years, months and days from someone’s birthdate.

Getting Years, Months and Days from a Birthdate

As mentioned above in the previous chapter, you can get the number of years, months and days from someone’s birthdate. This result is thanks to the DATEDIF function in Microsoft Excel.

By using the unit options in the DATEDIF function, you can choose the unit of time to calculate the age of the person. There are three different formulas in order to calculate the years, months and days respectively:

In order to calculate the number of years enter the formula of:
=DATEDIF(start_date, end_date, “Y”)

In order to calculate the number of months enter the formula of:
=DATEDIF(start_date, end_date, “YM”)

In order to calculate the number of days enter the formula of:
=DATEDIF(start_date, end_date, “MD”)

Each of these formulas will give a different type of unit of time. However, if you prefer the formula shows you each of the units in the same cell, you have to combine all three of these. However, the only addition would be the ‘and (&)’ symbols and the specification of the unit of time. You can find the formula below:

=DATEDIF(start_date, end_date, “Y”) & “YEARS,” & DATEDIF(S_D, E_D, “YM”) & “MONTHS,” & DATEDIF(S_D, E_D, “MD”) & “DAYS”

This formula can seem too long and too complicated to write down. But do not worry, although it seems complicated on the outside, you would be entering the same formulas listed above. You have to put the ‘and (&)’ symbol between the formulas and clarify the unit of time you want to see. The result of this formula would be as such:

45 Years, 2 Months, 12 Days

Calculating Age with Only a Certain Year

The reason these specific formulas are given in this blog post is that Microsoft Excel does not have recommended formulas regarding the DATEDIF function. That is why these exact formulas in order to calculate age in different ways will be extremely helpful for you.

Calculating age with only the data of a certain year can come handy if you do not have the month or the day data from that specific year. If you do not know the month and the day of the given year, you can just enter this formula and take January 1^st as the temporary date in order to calculate someone’s age:

=DATEDIF(start_date, end_year, (1, 1), “y”

Those “(1, 1)” values are the indicator that the temporary date of January (the first month) 1^st (the first day) is used. You can change that value and the date to calculate or change the age of someone. This formula and function is always used if the ‘end date’ is not known, only the data of ‘end year’ is given.

FINAL WORDS

So, the answer to the question ‘Can Excel Calculate Age?’ without a doubt is a ‘Yes!’. Everyone calculates the age of their own, someone they know or someone they know of. It is almost a daily activity and it can be even thought of wonder. You can do the math in your head, or write it down to make it sure you have an accurate result. Or you can also use online tools like Age Calculator to find a particular age, but for sure, you will need to do it one-by-one if you have a long list. So, if you are dealing with many birthdates and different people, you should do your age calculations on a digital spreadsheet. And the best one out there is Microsoft Excel.