Totalling Formulas

[belczyk]

I am creating a spreadsheet that calculates a person's time on the
job.

It looks like this

Inclusive Dates (Begin) Inclusive Dates (End) Totals
1/27/1996
3/20/1996 0 years, 1 months, 22 days
Inclusive Dates (Begin) Inclusive Dates (End)
3/21/1996
2/6/1998 1 years, 10 months, 16 days

It already calculates the time between the Inclusive Dates (Begin) and
Inclusive Dates (End), now i want it to total to totals into a block
which reflects the same way like:

Inclusive Dates (Begin) Inclusive Dates (End) Totals
1/27/1996
3/20/1996 0 years, 1 months, 22 days
Inclusive Dates (Begin) Inclusive Dates (End)
3/21/1996
2/6/1998 1 years, 10 months, 16 days


Final Total

2 years , 0 Months, 8 days

It will need to sum the two formulas.
=DATEDIF(B4,C4,"y") & " years, " & DATEDIF(B4,C4,"ym") & " months, " &
DATEDIF(B4,C4,"md") & " days"
And
=DATEDIF(B6,C6,"y") & " years, " & DATEDIF(B6,C6,"ym") & " months, " &
DATEDIF(B6,C6,"md") & " days".

Can anyone help? Please. You can also email me at
(e-mail address removed)

Thanks.

 

[Pete_UK]

Try this:

=(DATEDIF(B4,C4,"y")+DATEDIF(B6,C6,"y")) & " years, " &
(DATEDIF(B4,C4,"ym")+DATEDIF(B6,C6,"ym")) & " months, " &
(DATEDIF(B4,C4,"md")+DATEDIF(B6,C6,"md")) & " days"

Hope this helps.

Pete

 

[belczyk]

Thanks Pete, I will try this.

 

[belczyk]

ok it worked, but got one other issue, it gave me a total of 1 years,
11 months, 38 days

There cant be 38 days in a month.

Thanks

 

[Pete_UK]

Okay, this one corrects for that:

=DATEDIF(B4+B6,C4+C6,"y")&" years, "&DATEDIF(B4+B6,C4+C6,"ym")&"
months, "&DATEDIF(B4+B6,C4+C6,"md")&" days"

but with your example data it gives:

2 years, 0 months, 10 days

It's always difficult using months, as there is not a fixed number of
days in a month. An alternative is to work in elapsed days and to
convert to years and days (but then again, how many days are there in
a year?)

Is this close enough?

Pete

 

[belczyk]

Okay, this one corrects for that:

=DATEDIF(B4+B6,C4+C6,"y")&" years, "&DATEDIF(B4+B6,C4+C6,"ym")&"
months, "&DATEDIF(B4+B6,C4+C6,"md")&" days"

but with your example data it gives:

2 years, 0 months, 10 days

It's always difficult using months, as there is not a fixed number of
days in a month. An alternative is to work in elapsed days and to
convert to years and days (but then again, how many days are there in
a year?)

Is this close enough?

Pete





- Show quoted text -

i SEE YEA IT DOESNT GIVE THE RIGHT CALC ????

 

[Pete_UK]

But what is the correct answer?

If you take B4 away from C4 you get 53 days (or 54 if you want to
include the start date as part of the period) and C6-B6 is 687 (or
688) days. The total of elapsed days is thus 740 (or 742), and if you
take 365*2 away from this you have 10 (or 12) days remaining. If you
take into account the fact that 1996 was a leap year and the first
period encompassed the leap day, then you would have 9 (or 11) days
plus 2 years. You say the answer should be 2 years and 8 days.

So, which is right?

Pete

 

[belczyk]

Got another question if I want to subtract a date from the formulas
output can I do that to give me a new date.


=(DATEDIF(B25,C25,"y")+DATEDIF(B27,C27,"y"))&" years,
"&(DATEDIF(B25,C25,"ym")+DATEDIF(B27,C27,"ym"))&" months,
"&(DATEDIF(B25,C25,"md")+DATEDIF(B27,C27,"md"))&" days"

-

=B20 ( Example 12/12/2006) which the individual Enters

 

[Pete_UK]

The formula produces a text answer, so you will not be able to perform
any arithmetic directly on it. I'm not exactly sure what you mean by
subtracting a date from the result, anyway - what does 2 years and 10
days minus 12/12/2006 actually mean? Do you want 2 years and 10 days
earlier than the date entered in B20?

Pete

 

[belczyk]

The formula produces a text answer, so you will not be able to perform
any arithmetic directly on it. I'm not exactly sure what you mean by
subtracting a date from the result, anyway - what does 2 years and 10
days minus 12/12/2006 actually mean? Do you want 2 years and 10 days
earlier than the date entered in B20?

Pete

Trying to subtract a period like 2 yrs 1 day from a set date like 1
apr 2001 giving me a value

 

[Pete_UK]

If you had elapsed days, like 740, then you could just subtract that
from the date to get the earlier date. So rather than mess about
further with the DATEDIF formula, you could just have:

=B20-B4-B6+C4+C6

and format the cell in date format.

Hope this helps.

Pete