Formulas with staff rota

ALMA76

New Member
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

<tbody>
</tbody>

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

navic

Board Regular
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'
Rich (BB code):
=SUM(D2,F2,H2,J2,L2,N2)
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>

Last edited:

Replies
5
Views
64
Replies
4
Views
93
Replies
3
Views
45
Replies
7
Views
56
Replies
4
Views
54