Total Years Mounths Days

[Guest]

I have a colum with lots of birthdays I need to add up all the time summed together from todays date. Another words all the people in this room have a total of 1123 years 3 mounths and 11 days.

 

[Norman Harker]

Hi Mark!

Here's one approach:

Entered as an array formula:
=SUM(DATEDIF((A1:A20),TODAY(),"y"))+INT((SUM(DATEDIF((A1:A20),TODAY(),"ym")))/12)&"
years
"&MOD(((SUM(DATEDIF((A1:A20),TODAY(),"ym")))+INT((SUM(DATEDIF((A1:A20),TODAY(),"md")))/(365.25/12))),12)&"
months
"&ROUND(MOD((SUM(DATEDIF((A1:A20),TODAY(),"md"))),365.25/12),0)&" days
"

Enter array formulas by pressing and holding down Ctrl + Shift and
then pressing Enter.

I've assumed 365.25/12 days in a month and the result in the total has
been rounded.

 

[AlfD]

Hi!

Not too difficult in principle, but you will need to make a couple of
decisions.

It is easy to turn the difference between two dates into a number of
days:

=today()-A1 will give the number of days between the date in A1
(formatted as date) and today.

Total number of days for the people in the room is the sum of all of
these quantities.

How to turn days into years and months?
Are you happy to treat every year as 365 days (ignore leap years)?
Probably, I suspect.
Are you happy to treat every month as 30 days, likewise?

If so, the following will "decode" days to years, months and days.

A100 contains sum of all days.
B100 | =int(A100/365) -- gives number of years.
C100 | =int((A100-365*B100)/30) -- gives no. of months.
D100 | =A100-365*B100-30*C100 -- gives no. of days.

It will be close, but not exact. If you want a measure of exactly how
many days, months and years people have lived, then it is a slightly
different problem.

At that point I would reach for a function date.diff from the
collection Morefunc downloadable free from
'http://perso.wanadoo.fr/longre/excel/pages/Downloads.htm#Morefunc.xll.'
(http://)

Alf

 

[Norman Harker]

Hi Mark!

Adjusted formula for the range you reported privately and to allow for
blanks in the data range:

=SUM(IF(ISBLANK(H2:H500)=FALSE,DATEDIF((H2:H500),TODAY(),"y")))+INT(SUM(IF(ISBLANK(H2:H500)=FALSE,(DATEDIF((H2:H500),TODAY(),"ym"))))/12)&"
years
"&MOD((SUM(IF(ISBLANK(H2:H500)=FALSE,(DATEDIF((H2:H500),TODAY(),"ym"))))+INT(SUM(IF(ISBLANK(H2:H500)=FALSE,(DATEDIF((H2:H500),TODAY(),"md"))))/(365.25/12))),12)&"
months
"&ROUND(MOD(SUM(IF(ISBLANK(H2:H500)=FALSE,(DATEDIF((H2:H500),TODAY(),"md")))),365.25/12),0)&"
days"

You might find it better to break it up into helper formulas
especially if there are any further complications.