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]

Hi,
I need to calculate work experience in years ,months, days. I did 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 t add up
several such results in order to have total years , months and days how can
i do it?

The formula you both gave did not work.. Here is the exact sample wha
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
2years 10 Months 2 days 1years 1 Months 19 days 1years 4 Months 2
days
now I want to sum up the years (e.g.2 years 10 month 2 days) that give
below of each column. Any ideas?

 

[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

 

[David Biddulph]

You seem to have replied to your own message, rather than to the person to
whom you are trying to reply, and you haven't told us what formula you tried
that didn't work, and you've also not told us in what way it didn't work.
Did it give an error message? Did it give what you thought was the wrong
result? If so, what result did you expect & what did you get?

One of the formulae which was suggested was
=datedif(sum(q18:t18);sum(q19:t19);"y") & ....
Did you try that? If so, what result did you get?
For me it gave 5 years 4 months 14days? What result did you want, and why?

 

[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!!!!!!!!!!