Add/Sum up months, years, dates
 

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?

Add/Sum up months, years, dates
 

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.



Delya wrote:

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

Add/Sum up months, years, dates
 

Delya wrote:
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...

Add/Sum up months, years, dates
 

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 a
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/wo...ars-dates.html

"Glenn" wrote:

Delya wrote:
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...
.

Add/Sum up months, years, dates
 

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?
--
David Biddulph


"Delya" wrote in message
...

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
2years 10 Months 2 days 1years 1 Months 19 days 1years 4 Months 26
days
now I want to sum up the years (e.g.2 years 10 month 2 days) that given
below of each column. Any ideas?
--
Delya

Delya;658519 Wrote:

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?

Add/Sum up months, years, dates
 

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


"David Biddulph" wrote:

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?
--
David Biddulph


"Delya" wrote in message
...

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
2years 10 Months 2 days 1years 1 Months 19 days 1years 4 Months 26
days
now I want to sum up the years (e.g.2 years 10 month 2 days) that given
below of each column. Any ideas?
--
Delya

Delya;658519 Wrote:

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?



.