**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>