Sum multiple time differences

claven123

Board Regular
Joined
Sep 2, 2010
Messages
83
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

Code:
=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


0:30SunMonTueWedThuFriSat
1234567Hrs Worked
Amy6:008:007:0015:307:0015:309:0015:307:0015:307:0015:309:0012:0043:00
Beth 7:0015:307:0015:307:0015:307:0015:307:0015:30 40:00

<colgroup><col><col span="14"><col></colgroup><tbody>
</tbody>


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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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?
 
Upvote 0
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
 
Upvote 0
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
 
Last edited by a moderator:
Upvote 0
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)
 
Last edited:
Upvote 0
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.

Code:
=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))
 
Last edited:
Upvote 0
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
 
Upvote 0
Must be an easier formula for this simple task
 
Last edited by a moderator:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top