Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Is there a better way to calculate a person's age?

0

Don posted another of his excellent tutorials recently, "Calculate Someone's Age in Excel".

It said you can use the formula "=YEAR(TODAY())- YEAR(<<cell reference>>)" when you know their date of birth and have that stored in <<cell reference>>)".

I think that tells you how old they'll be this year (after their birthday) really.

To see how old they are today, and have their date of birth in cell A5,  you can use:

=AND(MONTH(TODAY())>=MONTH(A5),DAY(TODAY())>=DAY(A5)) + YEAR(TODAY())-YEAR(A5)-1

(which avoids IF functions by using AND and minus 1).

Is there a better, less verbose, to get their exact age in years?

### CORRECTION:

See the less verbose formula proposed by Variatus, seems to work well- thanks Variatus (Don, I could not "Select" his answer not add to the discussions started by him/her or you for some reason so have put my comments in here).

The formula I suggested above doesn't work properly (sorry). The logic is flawed and should need to be the overly-long:

=OR(MONTH(TODAY())>MONTH(A5), AND(MONTH(TODAY())=MONTH(A5),DAY(TODAY())>=DAY(A5)))+YEAR(TODAY())-YEAR(A5)-1

or if like Variatus you have A1 with formula =TODAY() :

=OR(MONTH($A$1)>MONTH(A5), AND(MONTH($A$1)=MONTH(A5),DAY($A$1)>=DAY(A5)))+YEAR($A$1)-YEAR(A5)-1

Better to use the formula from Variatus!

exceldate-time
MS Office Version: Excel 2013 version 15048231000
posted by: John_R (rep: 9) on Fri Nov 10, 2017 at 8:53 am
Answer
Discuss

Discussion

This is precisely why I'm excited to allow for users to be able to add to and edit the tutorials on here, so we can add more great solutions like these! :)
don (rep: 1989) Nov 12, '17 at 10:19 am
Thank you for your accolade @John_R.
@Don Your excitement didn't boil over into giving my answer a thumbs-up though, lol:
Variatus (rep: 4889) Nov 13, '17 at 8:25 pm
John you should be able to select his answer now. There is just a bit of a delay from when a question is answered to when you are allowed to Select it.
Variatus hmmmm....... hmmm....... haha since it looks like you even accounted for leap years, definitely earned that vote. :)
don (rep: 1989) Nov 14, '17 at 3:24 am
Add to Discussion

Answers

1
Selected Answer

Try this formula, with the birthday in A5 and =TODAY() in A1.

=INT(($A$1-$A5+(MOD(YEAR($A$1),4)/4))/365.25)

You might replace both references to $A$1 with TODAY() directly in the formula. I didn't recommend that because if you have the date in a cell you can see and modify it whereas when it is in the formula modification is complicated and you can't see it at all.

posted by: Variatus (rep: 4889) on Fri Nov 10, 2017 at 10:01 pm
Discuss

Discussion

Thanks again Variartus for this working answer (and far less verbose than mine). Also thanks to Don for advising on the slight delay for responses to anwsers.
John_R (rep: 9) Nov 14, '17 at 4:08 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login