Formulas with staff rota

[ALMA76]

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?

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]SUN[/TD]
[TD]MON[/TD]
[TD]HOURS MINUS BREAK[/TD]
[TD]TUES[/TD]
[TD]HOURS MINUS BREAK[/TD]
[TD]WEDS[/TD]
[TD]HOURS MINUS BREAK[/TD]
[TD]THURS[/TD]
[TD]HOURS MINUS BREAK[/TD]
[TD]FRI[/TD]
[TD]HOURS MINUS BREAK[/TD]
[TD]SAT[/TD]
[TD]HOURS MINUS BREAK[/TD]
[TD]TOTAL WEEKLY HOURS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ALMA[/TD]
[TD][/TD]
[TD]07:00 - 16:00[/TD]
[TD][/TD]
[TD]07:00 - 16:00[/TD]
[TD][/TD]
[TD]07: - 16:00[/TD]
[TD][/TD]
[TD]07: - 16:00[/TD]
[TD][/TD]
[TD]07: - 16:00[/TD]
[TD][/TD]
[TD]07: - 16:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[navic]

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

=RIGHT(C2,5)-LEFT(C2,5)-0,0208333333333333

Formula in cell 'O2'

=SUM(D2,F2,H2,J2,L2,N2)

If you want results as a decimal number then try use this formula below

=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))-

[TABLE="width: 1096"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]SUN[/TD]
[TD]MON[/TD]
[TD]HOURS MINUS BREAK[/TD]
[TD]TUES[/TD]
[TD]HOURS MINUS BREAK[/TD]
[TD]WEDS[/TD]
[TD]HOURS MINUS BREAK[/TD]
[TD]THURS[/TD]
[TD]HOURS MINUS BREAK[/TD]
[TD]FRI[/TD]
[TD]HOURS MINUS BREAK[/TD]
[TD]SAT[/TD]
[TD]HOURS MINUS BREAK[/TD]
[TD]TOTAL WEEKLY HOURS[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]ALMA[/TD]
[TD][/TD]
[TD]08:00 - 16:00[/TD]
[TD]7:30[/TD]
[TD]07:00 - 16:00[/TD]
[TD]8:30[/TD]
[TD]07:00 - 16:00[/TD]
[TD]8:30[/TD]
[TD]07:00 - 16:00[/TD]
[TD]8:30[/TD]
[TD]07:00 - 19:00[/TD]
[TD]11:30[/TD]
[TD]07:00 - 16:00[/TD]
[TD]8:30[/TD]
[TD]53:00:00[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]DEC hours[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0,020833333[/TD]
[TD] =30 min[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7,5[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0,041666667[/TD]
[TD] =1 h[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]