# Ages using Excel

Liteman 16:15 08 Jun 09
Locked

I have created a small spreadsheet holding details of birth dates of a group of people. I have tried to set it up such that whenever the sheet is opened it shows the current age of each person. I've basically used 3 columns for the date data. Col C is the date of birth, col D is today's date =NOW()and col E is their age =(D15-C15)/365 . This is all reasonably accurate except that it doesn't take into account leap years and when a date is firly close to someones birthday it can report the wrong answer for age, especially when we are talking of people, shall we say, of a certain age.

Any hints as to haow I can take leap years into account?

Thanks

VoG II 16:22 08 Jun 09

Try

=DATEDIF(C15,D15,"y")&" y " &DATEDIF(C15,D15,"ym")&" m "&DATEDIF(C15,D15,"md")&" d"

Liteman 16:41 08 Jun 09

Thanks VOG - simple when you know how.

One more small teaser, there are 20 people on the list, how do I work out the average age and is it possible for the ages in your first formula to be given as number of years to 2dp as opposed to y m d?

Thanks

VoG II 16:50 08 Jun 09

To get the age to 2 dp try

=ROUND(DATEDIF(C15,D15,"y")+DATEDIF(C15,D15,"yd")/365.25,2)

Note that this may be slightly out as we don't know exactly how many leap years there are between C and D = the division by 365.25 is an approximation.

To get the average

=AVERAGE(E1:E20)

Liteman 16:58 08 Jun 09

Thanks VoG - works a treat

This thread is now locked and can not be replied to.

Sniper Elite 4 review: Headshotting Nazis has never felt so good

1995-2015: How technology has changed the world in 20 years

The Best Design, Illustration, Animation and VFX Awards of 2017

WWDC 2017 dates: How to get WWDC 2017 tickets, when is WWDC 2017 and more details announced