Formulas with staff rota

ALMA76

New Member
Joined
Jun 30, 2019
Messages
1
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?

SUNMONHOURS MINUS BREAKTUESHOURS MINUS BREAKWEDSHOURS MINUS BREAKTHURSHOURS MINUS BREAKFRIHOURS MINUS BREAKSATHOURS MINUS BREAKTOTAL WEEKLY HOURS
ALMA07:00 - 16:0007:00 - 16:0007: - 16:0007: - 16:0007: - 16:0007: - 16:00

<tbody>
</tbody>
 

Some videos you may like

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
Joined
Jun 14, 2015
Messages
249
Office Version
  1. 2013
Platform
  1. Windows
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))-

ABCDEFGHIJKLMNO
1SUNMONHOURS MINUS BREAKTUESHOURS MINUS BREAKWEDSHOURS MINUS BREAKTHURSHOURS MINUS BREAKFRIHOURS MINUS BREAKSATHOURS MINUS BREAKTOTAL WEEKLY HOURS
2ALMA08:00 - 16:007:3007:00 - 16:008:3007:00 - 16:008:3007:00 - 16:008:3007:00 - 19:0011:3007:00 - 16:008:3053:00:00
3
4
5DEC hours0,020833333 =30 min
67,50,041666667 =1 h

<tbody>
</tbody>
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,517
Messages
5,523,349
Members
409,512
Latest member
Exceldoktor

This Week's Hot Topics

Top