Totaling Time for the Week

[Andrew B]

Good Morning-
I have watched many of the videos and have made some strides in totaling my time allotments on my employee schedule, but I am still struggling with a complete formula because, as I have it written, excel will not total my hours above 24 with out adding in "+24" to the end of the formula when necessary. I have tried to perform this using the IF function, however I cannot seem to create the appropriate logical test. Below is the formula I am using for under 24 hours total:

=MOD(SUM(C5:C11)-SUM(B5:B11),1)*24 & " Hours"

Here is the formula when an employee is over 24 total hours:

=MOD(SUM(C5:C11)-SUM(B5:B11),1)*24+24 & " Hours"

Anyone that could help me make this all encompassing would be a superstar. FILE ATTACHED.

 

[vletm]

Andrew B
The 1st version ...
and if NO over midnight shifts!

 

[r2c2]

@Andrew B It looks like your emps all have time in < time out (ie no shifts spanning midnight). If so, you can use this simple formula to calculate the total hours.

=SUMPRODUCT(C5:C11-B5:B11)

If you will also have shifts spanning mid night, use this array formula

=SUM(C5:C11-B5:B11+IF(C5:C11>=B5:B11,0,1))

Both formulas give result of duration in days. So to see the result in hours, use number format code [h]:mm "hours"