Time Sheet with Formula cell to work out the hours work
Hi

I have create a spreadsheet so that I can do rota for the week for about 20 staffs.

I have 3 columns for each day of the weeks.

Example:
Monday headed - Start time - End time - Total hours worked deducting break.
Tuesday Headed - Start time - End time - Total hours worked deducting break.

Monday start time 7:45 to 22:00 end time = 14.25hrs - break 1 hour is 13.25hrs work

The formula I have use for the total hours work deducting break is =((F4-E4)*24)-IF((((F4-E4)*24)>8),1,IF((((F4-E4)*24)>6),0.5,0))

My issue now is that if a person close the shop they cant open because of the working time directive which is 11hrs in between.

I want the cell to highlight if Jack is closing the branch on Monday and for any reason he is opening on Tuesday at 7:45 the cell will indicate me there is a working time directive breach because he didn't have the 11hrs rest.

Also the amount of break entitle has been change. Can you help me to update my formula.
=((F4-E4)*24)-IF((((F4-E4)*24)>8),1,IF((((F4-E4)*24)>6),0.5,0))

before up to 6hrs no break
now up to 5.45 no break

thanks