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>
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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:
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top