Working with dates in Excel

  mabas17 17:32 03 Sep 2009
Locked

I am having problems working on a spreadsheet of dates and am looking for help. I am creating a file to show membership ages and senority in our area. Here is my setup.
The first column: "Members name". Second column: "Date of birth". Third column: DOB converted to automatically updated "Age" by Year and Month with "Datedif". Fourth column: "Hire date". Fifth column: Hire date automatically converted to "Length of Senority" by Year and Month with "datedif".

I want to get the totals and averages of the Age and Senority columns. I think I have the averages worked out by using =AVERAGE(B2:B49) and then converting that with =DATEDIF(B53,TODAY(),"Y")&" Yr, "&DATEDIF(B53,TODAY(),"YM")&" M".

But I am not having any luck with the totals.
Can anyone help?

  VoG II 17:47 03 Sep 2009

It is rather difficult to visualise this.

Could you upload the file (with the members names removed) to a site like click here and provide a link to download it.

  mabas17 04:38 04 Sep 2009

I haven't used this before so try this:

click here

  VoG II 15:58 04 Sep 2009

I've 'cheated' by using decimal ages.

In D2 copied down

=(TODAY()-B2)/365.25

then the sum is given by

=SUM(D2:D48)

Similarly you can use column G for the years of experience.

  mabas17 17:19 04 Sep 2009

VoG,
Thanks for the help but I would like 1 more step. I would like to take those decimal ages and senority and convert them back to years and months. I get over 690.688 of senority in decimal format but when I convert it with the "Datedif" I get 107 yr, 9 mth. Same with the ages.

  VoG II 17:27 04 Sep 2009

With decimal years in D52 try

=IF(INT(ROUND(D52*12,0)/12),INT(ROUND(D52*12,0)/12)&" Y ","")&IF(MOD(ROUND(D52*12,0),12),MOD(ROUND(D52*12,0),12)&" M","")

  mabas17 17:57 04 Sep 2009

VoG,

THANK YOU.
That was way over my head. It works great.

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

Elsewhere on IDG sites

Intel Coffee Lake release date and specifications

12 Amazing British Craft Beer Label Designs

watchOS 4 review

Les meilleurs navigateurs internet 2017