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