Formulas with staff rota
Results 1 to 2 of 2

Thread: Formulas with staff rota
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Formulas with staff rota

    Good evening all,

    I am trying to create a simple weekly rota for my team that will add up the amount of hours worked each day minus their breaks and also to add up their total hours for the week.
    Can anyone help me with the formulas please?

    SUN MON HOURS MINUS BREAK TUES HOURS MINUS BREAK WEDS HOURS MINUS BREAK THURS HOURS MINUS BREAK FRI HOURS MINUS BREAK SAT HOURS MINUS BREAK TOTAL WEEKLY HOURS
    ALMA 07:00 - 16:00 07:00 - 16:00 07: - 16:00 07: - 16:00 07: - 16:00 07: - 16:00

  2. #2
    Board Regular navic's Avatar
    Join Date
    Jun 2015
    Location
    Europa - Croatia
    Posts
    201
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with formulas with staff rota

    Quote Originally Posted by ALMA76 View Post
    Can anyone help me with the formulas please?
    One way of solution
    Working hours start / end should be the same format
    Formula in cell 'D2' if the duration of the pause is 30 minutes.
    Formula copy to F, H, J, L, N columns
    Code:
    =RIGHT(C2,5)-LEFT(C2,5)-0,0208333333333333
    Formula in cell 'O2'
    Code:
    =SUM(D2,F2,H2,J2,L2,N2)
    If you want results as a decimal number then try use this formula below
    Code:
    =IFERROR(HOUR(IF(TIMEVALUE(RIGHT(C2,5))>TIMEVALUE(LEFT(C2,5)),IF(TIMEVALUE(RIGHT(C2,5)) thenless 
    TIMEVALUE(LEFT(C2,5)),TIMEVALUE(RIGHT(C2,5))+24,TIMEVALUE(RIGHT(C2,5))-TIMEVALUE(LEFT(C2,5))),IF(TIMEVALUE(RIGHT(C2,5)) thenless 
    TIMEVALUE(LEFT(C2,5)),TIMEVALUE(RIGHT(C2,5))+24,TIMEVALUE(RIGHT(C2,5))-TIMEVALUE(LEFT(C2,5)))-TIMEVALUE(LEFT(C2,5))))+
    (MINUTE(IF(TIMEVALUE(RIGHT(C2,5))>TIMEVALUE(LEFT(C2,5)),IF(TIMEVALUE(RIGHT(C2,5)) thenless TIMEVALUE(LEFT(C2,5)),
    TIMEVALUE(RIGHT(C2,5))+24,TIMEVALUE(RIGHT(C2,5))-TIMEVALUE(LEFT(C2,5))),IF(TIMEVALUE(RIGHT(C2,5)) thenless 
    TIMEVALUE(LEFT(C2,5)),TIMEVALUE(RIGHT(C2,5))+24,TIMEVALUE(RIGHT(C2,5))-TIMEVALUE(LEFT(C2,5)))-TIMEVALUE(LEFT(C2,5))))/60),0)-0,5

    In last formula, pay attention to the "thenless" character. Forum slashes formula presentation.
    btw: Break can be
    0,020833333 = 30 min
    0,041666667 = 1 h

    A B C D E F G H I J K L M N O
    1 SUN MON HOURS MINUS BREAK TUES HOURS MINUS BREAK WEDS HOURS MINUS BREAK THURS HOURS MINUS BREAK FRI HOURS MINUS BREAK SAT HOURS MINUS BREAK TOTAL WEEKLY HOURS
    2 ALMA 08:00 - 16:00 7:30 07:00 - 16:00 8:30 07:00 - 16:00 8:30 07:00 - 16:00 8:30 07:00 - 19:00 11:30 07:00 - 16:00 8:30 53:00:00
    3
    4
    5 DEC hours 0,020833333 =30 min
    6 7,5 0,041666667 =1 h
    Last edited by navic; Jul 1st, 2019 at 02:59 AM. Reason: correct delimiter

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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