Add/Sum up months, years, dates

[Delya]

Hi,
I need to calculate work experience in years ,months, days. I did a formula
=DATEDIF(Q18;Q19;"y")&" years "&DATEDIF(Q18;Q19;"ym")&" months
"&DATEDIF(Q18;Q19;"md")& "days", where Q18 is 03.09.2007 and Q19 is
05.07.2008. I got a result 0 years 10 months and 2 days. I now want to add up
several such results in order to have total years , months and days, how can
i do it?

 

[Dave Peterson]

Try adding all the From dates and all the To dates and then use datedif against
that:

=datedif(sum(q18:t18);sum(q19:t19);"y") & ....

I'm not sure how big those sums have to be before =datedif() breaks, though.

 

[Glenn]

Hi,
I need to calculate work experience in years ,months, days. I did a formula
=DATEDIF(Q18;Q19;"y")&" years "&DATEDIF(Q18;Q19;"ym")&" months
"&DATEDIF(Q18;Q19;"md")& "days", where Q18 is 03.09.2007 and Q19 is
05.07.2008. I got a result 0 years 10 months and 2 days. I now want to add up
several such results in order to have total years , months and days, how can
i do it?

Assuming your dates are across rows 18 and 19, try this array formula (commit
with CTRL+SHIFT+ENTER):

=DATEDIF(,SUM(Q18:V18-Q19:V19),"y")&" years "&
DATEDIF(,SUM(Q18:V18-Q19:V19),"ym")&" months "&
DATEDIF(,SUM(Q18:V18-Q19:V19),"md")& " days"



If the dates are arranged alternating down the column, say Q18:Q29, it gets a
little more complex (still an array formula):


=DATEDIF(,SUM((Q18:Q29*((-MOD(ROW(Q18:Q29),2))+1))-
(Q18:Q29*MOD(ROW(Q18:Q29),2))),"y")&" years "&
DATEDIF(,SUM((Q18:Q29*((-MOD(ROW(Q18:Q29),2))+1))-
(Q18:Q29*MOD(ROW(Q18:Q29),2))),"ym")&" months "&
DATEDIF(,SUM((Q18:Q29*((-MOD(ROW(Q18:Q29),2))+1))-
(Q18:Q29*MOD(ROW(Q18:Q29),2))),"md")& " days"



If they are arranged some other way...

 

[Delya]

My dates are accross rows, but the formula you both gave did not work.. Here
is the exact sample what i need
Column A Column B Column C
18.11.1992 13.09.1995 26.12.1996
20.09.1995 01.11.1996 22.05.1998
Using Dateif formula i got below correct results:
Results are:
column A-2years 10 Months 2 days
Column B-1years 1 Months 19 days
Coumn C- 1years 4 Months 26 days and goes on

now I want to sum up the years that given below of each column.
(e.g.2 years 10 month 2 days+1 years 1months 19 days+...)Any ideas?

'Original Source:
http://www.thecodecage.com/forumz/worksheet-functions/183814-add-sum-up-months-years-dates.html

 

[Delya]

yes i do not know how my reply was twice repeated.nevermind

I used =dateif(sum(q18:t18)...;it gave me #VALUE, then i corrected the
formula, and it worked!!!!!!!!!!!!!!!!!!!!!!!!!!1
Thanks much!!!!!!!!!!