Thread: average age in years, months & days

using =DATEDIF(BH2,AB2,"y") &" years," & DATEDIF(BH2,AB2,"ym") &" months," & DATEDIF(BH2,AB2,"md") & " days"

displays an age calculated from dates in BH2 and AB2 in a neat format in a 3rd cell where BH is the clients DOB, AB the date they were 1st seen in a clinic.

What I want is to be able to enter similar pairs of dates into BH3 & AB3, BH4 & AB4 etc and then determine the average age of a group of clients when they first came to the clinic.

Any ideas anyone?

Thanks

If your range goes through row 10, try: =Average(AB2:AB10-BH2:BH10), confirmed with Ctrl+Shift+Enter. Then go to format cells, custom, and type y "years, "m "months, "d "days" to format it as in the datedif formula.

Hi, yes this is the sort of thing but I'm not sure it's working quite right as if I set it up for two people, making one exactly 80 and 1 exactly 79, it returns the average as 79 years 7 months 1 day.

For my complete data set it returned an average age of 42 years, 12 months and 15 days which can't be right.

Any furher suggestions gratefully received

Hi, thanks - it nearly works.

Using date of clinic visit as 01/01/2008 and putting in two clients with DOB as 01/01/1938 and 01/01/39 shows ages as 70 and 69 with

=DATEDIF(BH43,AB43,"y") &" years," & DATEDIF(BH43,AB43,"ym") &" months," & DATEDIF(BH43,AB43,"md") & " days"

Thats fine.

Using MrChippy's formula gave the average age as 69 years 5 months 30 days which is fine.

However, if I set the second birth date to 01/06/1938, the ages become 70 exactly and 69 years 7 months meaning the average should be 69 years 9 months 15 days

BUT the formula returns 70 years, 9 months 15 days.

I don't see where the 'extra' year has come from in the 'years' part of the result...

Thanks

Hello charlie,

Works fine for me when I tested, as I would expect. Can you make sure you have the right dates, are you averaging the correct cells?

If you still can't get it to work then post the exact formula you're using

If it helps, it also doesn't work if I set the DOBs the same for two people and change the clinic date for one to ten years different from the other. That shows their ages 10 years apart (60 and 70) at time of clinic but shows the average of the two ages as 69 not 65.

Sorted!! I must have looked at it 20 times without spotting the error but when you said it worked I had one more check - and found the typo!!

Thanks so much, this is going to make my life much easier!!