Auto summing hours and minutes.Conditional formatting cell with the sum of time attendance

[PhilB62]

Ok, 2 problems on the same spreadsheet. I am doing an attendance record.

Chadwell Pay Data. Week 2 12th August - 18th August. 2.xlsx


I cannot get the auto sum of hours and minutes worked in row 25. Is there a special formula I need to use to add 'time' together. I want a total of E5, E8, E11, E14, E17, E20 and E23 in Hours and minutes.

Second problem is I want the days when people worked, I would like there hours worked in RED. I chose the conditional format greater than 0:01 = highlight bold red. It has conditional formatted ok on the day shift side where its a simple (finish time - start time). but on the night shift side i get the hours worked by (finish time - start time) + 12. This cell will not conditional format. Anyone know why??

Be gentle please.

Thanks you

[ramananhrm]

I have used the Sumproduct to resolve the query.

[newdoverman]

I re-worked the formulae in the hours worked rows to allow time periods that cross midnight. This allows consistent formulae across your worksheet without having to change the formula for those particular instances. This also allows a slightly simpler formula to calculate total hours. In addition, your Conditional Formatting now works for all cells that it is applied to.