Calculate Someone's Age

How to Calculate Someone’s Age in Excel

Joseph Excel 0 Comments

Calculating someone’s age in Excel should be pretty straightforward. You would think there was an Age() function in Excel, but unfortunately, we’re on our own to figure this one out. No worries, though, it’s not as bad as you might think.

Let’s get started.

The Problem

Say you have someone’s birthdate such as 4/4/1988. How would you go about calculating their age?

You might say something like “easy, just take today’s date and subtract the birthdate.” Well, that’s a good starting point. Let’s work with that.

Assuming the birthdate is in A1, we’ll take today’s date (using the TODAY() function) and subtract the birthdate:

=TODAY()-$A$1
=5/5/29 (May 5th, 1929)

say whaaaaat

The result is May 5th, 1929? How did that happen?

How dates work in Excel

It’s important to take a second to explain how dates work in Excel. Put the number 1 in a cell and convert it to a date. It will display 1/1/1900. This is because dates in Excel start from 1/1/1900. If you changed the cell by putting a 2 in there, the date would become 1/2/1900. What this means is that each number represents how many days it has been since 1/1/1900.

Let’s look at the formula when we convert the dates into numbers.

=TODAY()-$A$1
=8/8/2017 - 4/4/1988
=42955 - 32237
=10718

If you put the number 10718 in a cell and format it as a date you’d get 5/5/29.

But what does the number 10718 represent, anyway?

It actually represents the number of days between 4/4/1988 and 8/8/2017 (including leap days). So this person who has a birthdate of 4/4/1988 has lived for 10,718 days. We can use this to figure out how many years they are. We just need to divide by 365.25 (days in a year – including the fraction for leap years).

Calculating Someone’s Age the Right Way

Let’s adjust the formula slightly

=(TODAY()-$A$1)/365.25
=29.3442847

This person is 29 years old. The fraction portion represents how much of a percentage of a new year they have lived. In our formula, this would be ~34% of a new year, which translates to about 4 months. So 29 years and 4 months of age.

From here, we can either fix the number format to not include fractions or adjust the formula one last time:

=ROUND((TODAY()-$A$1)/365.25,0)

And that’s how you calculate someone’s age in Excel.

Are you ready to master Excel Dashboards?

Learn Excel Dashboard Course Power BI Webinar

Still not convinced? Check out my review of the course!