Ages using Excel

  Liteman 16:15 08 Jun 2009
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 2009

Try

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

  Liteman 16:41 08 Jun 2009

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 2009

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)

adjust the range to suit.

  Liteman 16:58 08 Jun 2009

Thanks VoG - works a treat

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

Elsewhere on IDG sites

Dell XPS 13 9370 (2018) review

Creative studio Omnibus' brand identity for We Said Enough fights back against sexual misconduct

WWDC history: Apple's product launches since 2005

Espace de stockage : comment libérer de la mémoire sur votre iPhone ?