Sum multiple time differences

[claven123]

I have a calendar and I'm trying to calculate the hours worked.

In cells C7 and D7 are start and stop times for Saturday and E7 start , F7 stop... for Monday. This repeats across to Sunday at O7 and P7.

in Q6 is this formula

=SUM((D7-C7),(F7-E7-$B$4),(H7-G7-$B$4),(J7-I7-$B$4),(L7-K7-$B$4),(N7-M7-$B$4),(P7-O7))

I subtract the lunch break as set in B4 as 0:30, only for weekdays, as weekends are short shifts without a lunch break.

I have a custom formatting on all the cells, [h]:mm


Interestingly, if I enter 07:00 in E7 and 15:30 in F7 I get 6:00, but if I enter those times for all 5 weekdays I get 40:00
If I enter those times in two days, say Monday and Tuesday, then I get 14:30


[TABLE="width: 724"]
<colgroup><col><col span="14"><col></colgroup><tbody>[TR]
[TD]0:30[/TD]
[TD="colspan: 2"]Sun[/TD]
[TD="colspan: 2"]Mon[/TD]
[TD="colspan: 2"]Tue[/TD]
[TD="colspan: 2"]Wed[/TD]
[TD="colspan: 2"]Thu[/TD]
[TD="colspan: 2"]Fri[/TD]
[TD="colspan: 2"]Sat[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]1[/TD]
[TD="colspan: 2"]2[/TD]
[TD="colspan: 2"]3[/TD]
[TD="colspan: 2"]4[/TD]
[TD="colspan: 2"]5[/TD]
[TD="colspan: 2"]6[/TD]
[TD="colspan: 2"]7[/TD]
[TD]Hrs Worked[/TD]
[/TR]
[TR]
[TD]Amy[/TD]
[TD="align: right"]6:00[/TD]
[TD]8:00[/TD]
[TD="align: right"]7:00[/TD]
[TD]15:30[/TD]
[TD="align: right"]7:00[/TD]
[TD]15:30[/TD]
[TD="align: right"]9:00[/TD]
[TD]15:30[/TD]
[TD="align: right"]7:00[/TD]
[TD]15:30[/TD]
[TD]7:00[/TD]
[TD]15:30[/TD]
[TD]9:00[/TD]
[TD]12:00[/TD]
[TD]43:00[/TD]
[/TR]
[TR]
[TD]Beth[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]7:00[/TD]
[TD]15:30[/TD]
[TD="align: right"]7:00[/TD]
[TD]15:30[/TD]
[TD="align: right"]7:00[/TD]
[TD]15:30[/TD]
[TD="align: right"]7:00[/TD]
[TD]15:30[/TD]
[TD]7:00[/TD]
[TD]15:30[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]40:00[/TD]
[/TR]
</tbody>[/TABLE]


When doing the lit review, I see several references to adding times, and differences, but not when they are combined. I thought it was a formatting issue. If I cut the formula down to only one day, then the value is correct, but when I have many of the (H7-G7-$B$4) I get the error/wrong value.




Thanks,

D

 

[arthurbr]

I enter 07:00 in E7 and 15:30 in F7 I get 6:00

Shouldn't you get 8:00 ?
All results look correct...
Beth worked 5 times 8 hr ( totalling 40) and the total for Amy is also OK
I don't understand what is wrong?

 

[claven123]

I think I may have figured it out, if the cells are blank it STILL is subtracting the lunch in B4. Not sure how to fix that? Multiple if then functions?

D

 

[Legacy 421546]

It's because 00:30 is subtracted regardless of if the corresponding cells are filled in or not.
You need to add something like IF(cells empty, 0, -B4)
.
Or better, add a lunch break cell foreach weekday

 

[arthurbr]

Perhaps use something like H7-G7-$B$4*((H7-G7>0)
Adapted to each difference

But it would probably be easier, (to provide for weekends) to rearrange your data with the Name in ColA, date in colB, start time in colC and end time in colD
Col E would contain the difference like =D2-C2-$K$1*or(and(d2<>0;C2><0);weekday(B2)<6)
where K1 contains the break

(there must be an easier way)

 

[kvsrinivasamurthy]

For AMY. Data in C6:P6. Row 5 is 1,2,3,4,5,6,7 in alternate cells from C5.
Formula in R6 then drag down.

=SUMPRODUCT((($D6:$P6)*(MOD(COLUMN($D6:$P6),2)=0)*($D6:$P6<>""))-(($C6:$O6)*(MOD(COLUMN($C6:$O6),2)=1)*($C6:$O6<>""))-(($C$5:$O$5<>1)*($C$5:$O$5<>7)*(MOD(COLUMN($C6:$O6),2)=1)*($C6:$O6<>"")*$B$4))

 

[claven123]

I don't follow this. However, it worked.



=SUMPRODUCT((($D6:$P6)*(MOD(COLUMN($D6:$P6),2)=0)*($D6:$P6<>""))-(($C6:$O6)*(MOD(COLUMN($C6:$O6),2)=1)*($C6:$O6<>""))-(($C$5:$O$5<>1)*($C$5:$O$5<>7)*(MOD(COLUMN($C6:$O6),2)=1)*($C6:$O6<>"")*$B$4))</pre>

Love to know how it works,

Thanks!!

d

 

[Legacy 421546]

Must be an easier formula for this simple task

 

[claven123]

Ok, so it seems this does not work entirely.

Row 5 is the date, so it changes from 1-31 across the week.

This changes the output in the right column. The hours total.

How do I adjust the formula for the varied dates.

d