Totalling Formulas

  • Thread starter Thread starter belczyk
  • Start date Start date
B

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.
 
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
 
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
 
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
 
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 -
Click to expand...

i SEE YEA IT DOESNT GIVE THE RIGHT CALC ????
 
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
 
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
 
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
 
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
Click to expand...

Trying to subtract a period like 2 yrs 1 day from a set date like 1
apr 2001 giving me a value
 
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
 
Back
Top