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

OnePlus 5 review

See the work of famous artists playing with toys

iPad Pro 10.5in (2017) review

Comment faire une capture d’écran sur un Mac ?