Shift bands

Jwinssss

New Member
Joined
Jan 17, 2024
Messages
18
Office Version
  1. 2019
Platform
  1. Windows
Hi everyone,

I am struggling to find a formula to calculate "shift premium times" for a 24/7 transport office.

I need to be able to calculate if a driver's work carries over three time bands, and if so, how many hours has been worked in each Time Band.

This would be for his week of work so driver there would be multiple start and finish times

Shift pattern Time Bands are:

Band A - 04:00 - 10:00
Band B - 10:00 - 18:00
Band C - 18:00 - 04:00
-------------------------------------------------------------

Example one;

Start 09:00
Finish 20:00

Driver has worked 1 hours Band A, 8 hours Band B, 1 hour Band C
--------------------------------------------------------------

But
Friday from 16:00 - 18:00 it’s Saturday rate
So it if a driver starts at 17:00 on the Friday it should say in the Saturday 1 hour Band B

Sat from 16:00 - 18:00 it’s Sunday rate
This should show on the Sunday rate
So if the driver starts at 17:00 it’s 1 hour Sunday band b

Sunday from 16:00 - 18:00 it’s Monday rate

Same as above

I can calculate the total number of hours worked, but not how many hours worked in each band... any help will be appreciated before I go completely mad

Thank you,
 
I learned by using Excel and tackling the problems presented to me. When I reached a point where I was stuck, I searched the internet and gathered ideas from others. You always pick up something new, whether it's a function you didn't know or a more efficient way to accomplish something you've already done in a more complicated manner. Once you've exhausted your own problems to solve, you can explore challenges on the Excel Championship website at Sample Cases – FMWC. Some of these cases are truly challenging and enjoyable. After completing them, you can come to this forum and try to help others with their problems and see the solutions from others. It's always enriching to see someone else's solutions and observe the different ways in which they resolved the same problem.
 
Upvote 0

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
1705911105444.png
 
Upvote 0
You still need to provide the information on de definitions of the bands and how they apply for each hour each day like I asked in post 9.
 
Upvote 0
@Jwinssss
I figured out a way with simple Excel functions that do not require any extra helper cells or user defined functions (like VBA or LET), but the formula for each Band is quite long and I still need to find a formula for Band C (which goes past midnight) and adjust the other formulas for the 16:00-18:00 "switch".

Would you like to have it that way or do the other methods suit you better?
If you would like to see my further formulas, please let me know.

1705923091298.png


Excel Formula:
E2 = IFS(AND(0.416667>B2; B2>=0.166667; 0.166667<C2; C2<0.416667); IF(AND(0.416667>B2; B2>=0.166667; 0.166667<C2; C2<0.416667); C2-B2; 0); AND(0.416667>B2; B2<=0.166667; 0.166667<C2;C2<0.416667); IF(AND(0.416667>B2; B2<=0.166667; 0.166667<C2;C2<0.416667); C2-0.166667; 0); AND(0.416667>B2; B2>=0.166667; 0.166667<C2; C2>0.416667); IF(AND(0.416667>B2; B2>=0.166667; 0.166667<C2; C2>0.416667); 0.416667-B2; 0); AND(0.416667>B2; B2<=0.166667; 0.166667<C2; C2>0.416667); IF(AND(0.416667>B2; B2<=0.166667; 0.166667<C2; C2>0.416667); 0.416667-0.166667; 0); TRUE; 0)
 
Upvote 0
Your driver table from post 13 should work fine, but lIke Felix said, you need to create a table with the band information. Ideally it should cover the whole week, like the examples we showed in post 3 and 9. Furthermore, you need to show us an example of what you want the results to look like. You can just manually mock up something and show us, and we can use that as a guide. But at this point, we're just guessing.
 
Upvote 0
1706201118586.png

These would be the times with the rates, So the Table from
#13 is what i would copy and paste onto the sheet and then have the script or formula work it out
 
Upvote 0
Yes you will have to complete the RateBands table with the actual rates (column S).

Here is the file to download:

ShiftBands.zip

ShiftBands.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
1Hours worked by bands
2DATESurnameForenameTime InTime OutTotal hoursTo be paid [USD]ABB-MonB-SatB-SunCC-MonC-SatC-SunBandRateDescriptionFromToMonTueWedThuFriSatSun
32023-09-02 (Sat)Driver 117:1502:409:25302.00----0:452:40--6:00A10.0000:0001:00CCCCCCC
42023-09-03 (Sun)Driver 117:1502:138:58268.25--0:45--2:136:00--B20.0001:0002:00CCCCCCC
50:000.00---------C30.0002:0003:00CCCCCCC
60:000.00---------B-Sat23.0003:0004:00CCCCCCC
70:000.00---------B-Sun24.0004:0005:00AAAAAAA
80:000.00---------B-Mon21.0005:0006:00AAAAAAA
90:000.00---------C-Sat33.0006:0007:00AAAAAAA
100:000.00---------C-Sun34.0007:0008:00AAAAAAA
110:000.00---------C-Mon31.0008:0009:00AAAAAAA
120:000.00---------09:0010:00AAAAAAA
130:000.00---------10:0011:00BBBBBBB
140:000.00---------11:0012:00BBBBBBB
150:000.00---------12:0013:00BBBBBBB
160:000.00---------13:0014:00BBBBBBB
170:000.00---------14:0015:00BBBBBBB
180:000.00---------15:0016:00BBBBBBB
1916:0017:00BBBBB-SatB-SunB-Mon
2017:0018:00BBBBB-SatB-SunB-Mon
2118:0019:00CCCCC-SatC-SunC-Mon
2219:0020:00CCCCC-SatC-SunC-Mon
2320:0021:00CCCCC-SatC-SunC-Mon
2421:0022:00CCCCC-SatC-SunC-Mon
2522:0023:00CCCCC-SatC-SunC-Mon
2623:0024:00CCCCC-SatC-SunC-Mon
Sheet1
Cell Formulas
RangeFormula
H2:P2H2=TOROW(SORT(RateBands[Band]))
F3:F18F3=A3+E3+(E3<D3)-(A3+D3)
G3:G18G3=LET( hoursWorkedPerBand, $H3#*24, bandsHeader, $H$2#, rates, BYCOL(bandsHeader, LAMBDA(x, XLOOKUP(x, RateBands[Band], RateBands[Rate]))), SUM(hoursWorkedPerBand*rates) )
H3:P18H3=LET(start, A3+D3, end, A3+E3+(E3<D3), bands, $X$3:$AD$26, bandsHeader, $H$2#, n, (end-start)/TIME(0,1,0), ts, VALUE(TEXT(SEQUENCE(n,, start, TIME(0,1,0)), "yyyy-mm-dd hh:mm")), dayIndex, WEEKDAY(ts,2), time, VALUE(TEXT(ts, "hh:mm")), hourIndex, INT(time/TIME(1,0,0))+1, bs, INDEX(bands, hourIndex, dayIndex), res, BYCOL(bandsHeader, LAMBDA(x, IFERROR(ROWS(FILTER(bs,bs=x)),0)*TIME(0,1,0))), res )
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H2:P2,R3:R11,X3:AD26Cell Value="C-Mon"textNO
H2:P2,R3:R11,X3:AD26Cell Value="C-Sun"textNO
H2:P2,R3:R11,X3:AD26Cell Value="B-Mon"textNO
H2:P2,R3:R11,X3:AD26Cell Value="B-Sun"textNO
H2:P2,R3:R11,X3:AD26Cell Value="B-Sat"textNO
H2:P2,R3:R11,X3:AD26Cell Value="C-Sat"textNO
H2:P2,R3:R11,X3:AD26Cell Value="C"textNO
H2:P2,R3:R11,X3:AD26Cell Value="B"textNO
H2:P2,R3:R11,X3:AD26Cell Value="A"textNO
 
Upvote 0
Hi Felix I have tried to use your sheet but if I change anything the formula changes to #NAME?
1706256556383.png
 
Upvote 0

Forum statistics

Threads
1,216,499
Messages
6,131,012
Members
449,613
Latest member
MedDash99

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