Working with dates in Excel

  mabas17 17:32 03 Sep 09
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 09

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 09

I haven't used this before so try this:

click here

  VoG II 15:58 04 Sep 09

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 09

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 09

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 09

VoG,

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

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

Nintendo Switch review: Hands on with the intuitive modular console and its disappointing games…

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

Here's what should be coming to Adobe Project Felix in 2017

Apple AirPods review: Apple's beautiful new Bluetooth headphones bring true intelligence to…