Results 1 to 4 of 4

Thread: Time Sheet

  1. #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

    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.

    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.

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


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


    Change the Hours worked formula to


  3. #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.


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


Posting Permissions

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