Totalling Hours devided by breaks

[ChefExcel]

Im currently finishing an excel sheet for a rota that calculates hours worked over a week and subtracts the breaks making a total in the final box

not going to plan, and the total box isn't adding the hours, the other formula i used, wouldnt input more then 24 hours

Any help appreciated

[Spencer101]

Hi,

Your totals formulas are slightly incorrect. Take cell AF7 for example, if you change the formula from =SUM(F7,J7,N7,R7,V7,Z7,AD7)-(G7,K7,O7,S7,W7,AA7,AE7) to =SUM(F7,J7,N7,R7,V7,Z7,AD7)-SUM(G7,K7,O7,S7,W7,AA7,AE7) and change the formatting to [HH]:MM it should do what you need.

One thing I would point out though, you're getting #N/A errors in the "Break" columns where there is no value in the relevant "Hours" column. This causes column AF to show errors until there are are values in all preceding columns. You can wrap those formulas in IFERROR() to clear that problem up.

[ChefExcel]

Works a charm Thank You!

Having problems with the wrapping of 'IFERROR' it says i have too few arguments :s.

I'd want the value of N/A to be corrected to 00:00, any ideas ?

[Spencer101]

Taking the formula in cell AE7 for example, change it to
=IFERROR((MATCH(AD7,1*{"3:59","5:59","8:59","10:59","12:59"})-1)*"00:15",0)

The bits in bold are the changes to your original formula.

Copy and paste that cell into all the relevant ones and it should do what you need.

[ChefExcel]

Excellent,

Finally completed. Thanks for all your help Spencer.

[Spencer101]

Not a problem. Glad to help a fellow foodie