Re: Help with formulas with staff rota
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
Rich (BB code):
=RIGHT(C2,5)-LEFT(C2,5)-0,0208333333333333
Formula in cell 'O2'
If you want results as a decimal number then try use this formula below
Rich (BB 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
<timevalue(left(c2,5)),timevalue(right(c2,5))+24,timevalue(right(c2,5))-
<timevalue(left(c2,5)),timevalue(right(c2,5))+24,timevalue(right(c2,5))-
<timevalue(left(c2,5)),timevalue(right(c2,5))+24,
<timevalue(left(c2,5)),timevalue(right(c2,5))+24,timevalue(right(c2,5))-timevalue(left(c2,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
</timevalue(left(c2,5)),timevalue(right(c2,5))+24,timevalue(right(c2,5))-timevalue(left(c2,5))),
</timevalue(left(c2,5)),timevalue(right(c2,5))+24,
</timevalue(left(c2,5)),timevalue(right(c2,5))+24,timevalue(right(c2,5))-
</timevalue(left(c2,5)),timevalue(right(c2,5))+24,timevalue(right(c2,5))-
| 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 | | | | | | | |
<tbody>
</tbody>