Sum multiple time differences

claven123

Board Regular
Joined
Sep 2, 2010
Messages
63
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
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

arthurbr

Well-known Member
Joined
Dec 8, 2006
Messages
2,004
Office Version
2010
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

Board Regular
Joined
Sep 2, 2010
Messages
63
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
 
L

Legacy 421546

Guest
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:

arthurbr

Well-known Member
Joined
Dec 8, 2006
Messages
2,004
Office Version
2010
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:

kvsrinivasamurthy

Well-known Member
Joined
Nov 6, 2013
Messages
628
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:

claven123

Board Regular
Joined
Sep 2, 2010
Messages
63
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
 
L

Legacy 421546

Guest
Must be an easier formula for this simple task
 
Last edited by a moderator:

claven123

Board Regular
Joined
Sep 2, 2010
Messages
63
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
 

Watch MrExcel Video

Forum statistics

Threads
1,100,209
Messages
5,473,170
Members
406,849
Latest member
gustavor0

This Week's Hot Topics

Top