Find a person’s age in Excel

September 6, 2004 / Filed under: Software

I’ve been working on an Excel project, and an early roadblock involved finding an immediate way to display a person’s current age, based solely on their birthdate.

Seems rather simple, doesn’t it? It seems something like this should be rather straight–forward.

It turns out it is, but only after I slaved over a complicated solution. I want to share what I went through first, though.

The blueprints

Before any project hits the computer, serious thinking needs to be done. If you can visualize exactly what you want to do, it will be that much easier to create.

I took some time to think about birthdates, and ages, and how I could structure the formula.

Materials

The only materials involved would be two separate dates. The first date would always be the current date. The second date would be the birthdate.

Subtract the years?

Initially, I thought I had an easy solution, and I was ready to move on to developing the application further, when I realized "just subtracting the years" was not accurate.

If we take two dates as an example, I can illustrate my point.

  1. Today is: 9/6/04
  2. A random birthday is: 9/15/90

Now, if we subtract the years (2004–1990), we get 14, which is the number of years in between. And that should always give us the person’s age, right?

Wrong. We have to look closer.

The birthdate in the example above is AFTER the current date. That means that the person has not yet had their birthday for this year. The person is still 13, until September 15.

So, just subtracting the years is not going to work. We have to perform some tests...

The formulas

We are OK, as long as the birthdate is ON or BEFORE the current date.

THEN... simply subtracing the years will work fine. But... if the birthdate is AFTER the current date, what do we do?

We have to write a statement, which sees if the birthdate is after the current date, and if it is, we subtract one from the original result:

IF( MONTH(birthdate)>MONTH(currentDate), YEAR(currentDate)-YEAR(birthdate) - 1, YEAR(currentDate)-YEAR(birthdate) )

After writing this forumla, I realized there was another hole.

What if their birthday was the same MONTH as the current month, but DAYS later? The formula above is only checking to see if the MONTH is different. In this case, the month would be the same, but the day would be different.

We have to throw more tests in there. This formula checks to see if the month of the current date is EQUAL TO the month of the birthdate. If the months are equal, then we check days...

IF(MONTH(birthdate)=MONTH(currentDate), IF(DAY(birthdate)>DAY(currentDate), YEAR(currentDate)-YEAR(birthdate) - 1, YEAR(currentDate)-YEAR(birthdate), IF(MONTH(birthdate)>MONTH(currentDate), YEAR(currentDate)-YEAR(birthdate) - 1, YEAR(currentDate)-YEAR(birthdate)) )

This formula seems to work fine, provided the "birthdate," and "currentDate" references are actually targeting cells with the dates in them.

Consult someone who knows

After doing all this work, I decided to ask a couple individuals who might know. One was Chad, and the other was a programmer at my current job. Both gentleman gave similar answers:

To find a person’s age, based solely on their birthdate, use this formula:

((currentDate)-(birthdate))/365.25

That was it.

And this extremely concise forumla actually works. The only difference between mine and theirs is theirs leaves a remainder, meaning you will get a result as such: 14.16667.

But, with Excel, remainders are easy to remove. You just format the cell to only show the whole number, with no numbers after the decimal place.

Comments/Mentions

# Jonathan at 8/3/2005 11:21 am cst

With the last formula, if you format the cell to only show the whole number, it will round it up a couple days before the birth day and you won't have the good person's age. Here's the exact formula you should use to find a person's age:

=DATEDIF(birthdate,now(),"y")

You won't find anything better than that ;)

# George at 10/25/2006 1:39 pm cst

If you remove the remainder using your method it will round UP if a person is 21.5 years old it will say they are 22.

Make sure you use =Floor(your_function,1) to round down.

# Matthom at 10/25/2006 2:48 pm cst

George, so you're saying that formatting the cell to only show the whole number will automatically round it at the same time?

I was under the impression that it simply drops the remainder, without doing any rounding.

So, 21.5 would be 21.

Is this an incorrect assumption?

# mark rollen mark at 8/30/2008 1:34 am cst

HEllo . you can use this very short formula... =DATEDIF(A2,TODAY(),"y") this wil solve the accurate age of a person :-) kramshock!

# mark at 9/1/2008 2:48 am cst

Sorry the 'A2' in my comment is birthdate

# Ross at 9/21/2008 8:25 am cst

leap years??????????