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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

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:

1,101,848
Messages
5,483,289
Members
407,391
Latest member
sumantskj

This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...