# Thread: Time Sheet

1. ## Time Sheet

Register for a FREE account, and/
or Log in to avoid these ads!

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

2. For the highlight, use conditional formatting with a formula of

=AND(E5<>"",(F4-E5)*24>11)

Change the Hours worked formula to

=(F4-E4)*24-((F4-E4)*24>5.75)*0.5-((F4-E4)*24>8)*0.5

3. Hi thanks

I have a bit of the problem with the formula you gave me for the working time directive highlight.

It does work the only issue is when I subtract the start shift to the previous shift it calculate it wrong. 9:00am minus 22:00pm = 13hrs instead 11hrs

how do I use MOD with your formula.

Please

4. MOD seems just as bad, but this seems to work

=AND(E5<>"",(1-F4+E5)*24>11)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•