summing times

[j.ruderman]

I'm tring to create a daily log/time sheet which will have start and stop
times in adjecent columns. My problem is there may, or may not, be mutiple
shift seprations with randomly located blank colmns seperating the end times
and begin times.
A B
6:00 am 07:00am
7:05 am 12:30pm
12:35 pm 2:00pm

4:30pm 7:00pm

9:00pm 10:30pm

=text(max(B:B),"h:mm")-text(min(A:A),"h:mm")

This works fine without mutiple shift times but in my line of work that is
very rare.
Like in his case the result would be 16.5 where only 12 hours were worked.

Please help.

 

[Bernie Deitrick]

j.,

Array enter (enter using Ctrl-Shift Enter) the formula

=SUM(B1:B10-A1:A10)

Or, with regular entry

=SUMPRODUCT(B1:B10-A1:A10)

Where B1:B10 are ending times, and A1:A10 are starting times. You may have
problems when times are wrapping aroung midnight - unless you also enter the
date with the time.

HTH,
Bernie
MS Excel MVP

 

[j.ruderman]

Thank you very much for the quick reply. I never would have guessed it to be
so simple. Both the Array and sumproduct seem to handle the midnight wrap
around as well.

Thanks again

 

[Bernie Deitrick]

Thank you very much for the quick reply. I never would have guessed it to
be
so simple. Both the Array and sumproduct seem to handle the midnight wrap
around as well.

Again, just to be clear, they only handle the midnight wrap if you have
entered date and times rather than just times - so check your entries and
results.

Bernie

 

[j.ruderman]

1st off, thank you for shedding the light on the sumproduct formula. Being a
novice self tought Excel user I've asked myself where this formula has been
all my life. However, I do have another Question about this sample sheet.
A B
1 7:00 AM 8:00 AM
2 8:05 AM 11:00 AM
3 11:10 AM 1:50 PM
4 2:00 PM 3:00PM

=sumprouduct(B:B)-sumproduct(A:A) in this case will have a result of 7:35
because of the differances between cells 4A & 3B and so on up the sheet. I
need, in this case, the result to be 8:00.
=(4A-3B)+(3A-2B)+(2A-1B) added to the sumproduct will work but each sheet
has 50 rows of times that may be filled in. Keep in mind my original question
therefore needing the sumproduct function.

 

[Bernie Deitrick]

j.,

You use just one SUMPRODUCT, not two:

=SUMPRODUCT(B1:B50-A1:A50)

HTH,
Bernie
MS Excel MVP

 

[j.ruderman]

Bernie,
I'm most likley still doing something wrong but its still giving me a result
of 7:35 as opposed to 8:00. If I change cell 2A to match 1B and so on down
the sheet it seems to work however each start and stop time must be accurate
to the minute.

 

[Bernie Deitrick]

j.,

I'm sorry, I misunderstood your problem. You could try this

=MAX(B1:B4)-MIN(A1:A4)

It seems that it will give you what you seem to want, but that is not a
strictly accurate summing of time differences, if the start times are in
column A and the end times in column B. The amount that you want to add
back in would seem to not be actual work time....maybe break time, or what
you are after is elapsed time rather than work time.???

HTH,
Bernie
MS Excel MVP

 

[David Biddulph]

It sounds as if all you want is =B4-A1 ? You are ignoring the rest of the
data in between.

 

[j.ruderman]

True with this sample sheet where a total of 8 hours are worked. My original
problem is that 8 hours total in a 24 hours period is rarely worked.
a b
1 7:00 am 10:00 am
2 10:10 am 1:30 pm
3 1:40 pm 3:00 pm
4
5 5:00 pm 7:00 pm
6
7 10:00 pm 11:30pm
blank rows being off duty hours.

=max(b:b)-min(a:a) will give a result of 16.5 where only 11.5 hours were
worked. This was nearly solved with =sumproduct(a:a-b:b) however, because 1B
and 2A and so on will always vary, it subtracts the total of the differances
throughout the sheet. I could add to the sumproduct()+(b1-a2) and so on but
this sheet is filled with up to 50 start and stop times. Hope this makes
sence and thank you again.

 

[Bernie Deitrick]

J,

Just one more SUMPRODUCT needed:

=SUMPRODUCT(B1:B7-A1:A7)+SUMPRODUCT((A2:A7<>"")*(B1:B6<>"")*(A2:A7-B1:B6))


Note the offset and slightly smaller row span of the A vs B cells...

HTH,
Bernie
MS Excel MVP

 

[j.ruderman]

Bernie,
It works great. Thank you very much for all your help.