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

1. ## 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. ## Re: Help with formulas with staff rota

Originally Posted by ALMA76
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