Shift bands

J

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,
 
Sort by date Sort by votes

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Ok, this should work:
Here the file to download: ShiftBands3.zip

This should work in Excel 2019, but I don't have that version anymore so I could not test it. Try it and let me know.

ShiftBands3.xlsx
CDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
1Hours worked by bands
2ForenameTime InTime OutTotal hoursTo be paid [USD]ABCB-SatB-SunB-MonC-SatC-SunC-MonBandRateDescriptionFromToMonTueWedThuFriSatSun
317:1502:409:25302.00--2:40-0:45--6:00-A10.0000:0001:00CCCCCCC
417:1502:138:58268.25--2:13--0:45--6:00B20.0001:0002:00CCCCCCC
5-----------C30.0002:0003:00CCCCCCC
6-----------B-Sat23.0003:0004:00CCCCCCC
7-----------B-Sun24.0004:0005:00AAAAAAA
8-----------B-Mon21.0005:0006:00AAAAAAA
9C-Sat33.0006:0007:00AAAAAAA
10C-Sun34.0007:0008:00AAAAAAA
11C-Mon31.0008:0009:00AAAAAAA
1209:0010:00AAAAAAA
1310:0011:00BBBBBBB
1411:0012:00BBBBBBB
1512:0013:00BBBBBBB
1613:0014:00BBBBBBB
1714:0015:00BBBBBBB
1815: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
Excel 2019
Cell Formulas
RangeFormula
H2:P2H2=TRANSPOSE(RateBands[Band])
F3:F8F3=A3+E3+(E3<D3)-(A3+D3)
G3:G8G3=SUM(($H$2:$P$2=RateBands[Band])*RateBands[Rate]*$H3:$P3)*24
H3:P8H3=IFERROR(SUM((INDEX($X$3:$AD$26, N(IF({1},INT(VALUE(TEXT((($A3+$D3)+(ROW(INDIRECT("A1:A"&ROUND((($A3+$E3+($E3<$D3))-($A3+$D3))/TIME(0,1,0),0)))-1)*TIME(0,1,0)), "hh:mm"))/TIME(1,0,0))+1)), N(IF({1}, WEEKDAY((($A3+$D3)+(ROW(INDIRECT("A1:A"&ROUND((($A3+$E3+($E3<$D3))-($A3+$D3))/TIME(0,1,0),0)))-1)*TIME(0,1,0)),2))))=H$2)*1)*TIME(0,1,0),0)
Press CTRL+SHIFT+ENTER to enter 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 it does work but is there anyway it could be sorted like the below you see how is brakes down the hours to the days worked? Please see the below this is from a website we compare the hours to make sure there correct ( there not always correct ) As you can see there's only 3 bands where on your one there's 9

As you can see the bottom 1 is broken down in the first picture.
I'm props being a jobsworth haha


1706277012730.png

1706277580202.png
 
Upvote 0
I don't think i understand what you mean.
You provided the day hour table:
1706280097232.png


Then I don't understand the image you posted. There are 3 band yes, but for the 13th of January. On the table below the image no date is 13th. How do I compare?
How do you want to add hours? What rate for what band? Can you describe it clearly?

I think the formulas do what you need. You have different ratings depend on the hour for each of each day. You sum up how many hours the driver worked for each rating and than you multiply this hour by the corresponding rating... and sum all up. Or is this not the case?
 
Upvote 0
The circled part is just the normal bands but to be payed on the next day so if the driver worked 16:30 on Friday it would be payed into the Saturday band
If it doesn't make sense then I will just make do I'm not sure how else to describe it
1706282197344.png


1706282361818.png
 
Upvote 0
So then i guess you have different rates for each day in the week? how are they? That information is missing.
What do a driver get paid for working 1 hour in each band (A, B, and C) each day of the week?

Completing this table:

BandMonTueWedThuFriSatSun
A???????
B???????
C???????
 
Upvote 0
Ohh, I think that I finally undestood.
Hope this table help to show what I think I undestood:

FromToMonTueWedThuFriSatSun
00:0001:00CCCCCCC
01:0002:00CCCCCCC
02:0003:00CCCCCCC
03:0004:00CCCCCCC
04:0005:00AAAAAAA
05:0006:00AAAAAAA
06:0007:00AAAAAAA
07:0008:00AAAAAAA
08:0009:00AAAAAAA
09:0010:00AAAAAAA
10:0011:00BBBBBBB
11:0012:00BBBBBBB
12:0013:00BBBBBBB
13:0014:00BBBBBBB
14:0015:00BBBBBBB
15:0016:00BBBBBBB
16:0017:00BBBBBBB
17:0018:00BBBBBBB
18:0019:00CCCCCCC
19:0020:00CCCCCCC
20:0021:00CCCCCCC
21:0022:00CCCCCCC
22:0023:00CCCCCCC
23:0024:00CCCCCCC


So the hours worked with the same color are paid on the day with the same color.
For example if the driver worked on Tuesday (I start with Tue because it the siemplest i think) from 10:00 to 20:00 he worked 8 hours in B band and 2 hours in C band, AND (the important part) he gets paid for those hours at the end of his shift this same Tuesday.
The same happens for Wednesday, and Thurday.
But on friday, lets say he works again from 10:00 to 20:00, then he worked again 8 hours in B band and 2 hours in C band, BUT... he gets paid on this same friday after his shift ONLY the hour from 10:00 to 16:00, that is a total of 6 hours in B band. The rest, that is 2 more hours in B Band and 2 more hours in C band add to the hours worked on Saturday and he gets them paid after his shift on Saturday.
Is this correct?
The same happens for saturday and sunday. That is, if he works hours from 16:00 to 24:00 he gets them paid the next day, and not after his shift.
Then the hours worked on sunday from 16:00 to 24:00 are paid on the next day, monday.

Did I finally understand it correctly? If that's so please let me know.
 
Upvote 0
Ok here it is:
File to download:ShiftBands4.zip

Now for this to work correctly you have to have all days that Jim (in this example) worked.
So one line for friday, one line for saturday, so that the hours from saturday are calculated adding the actual hours from saturday plus the B and C bands from friday.
The column B2 and C2 (K and L) are only helper columns. The hours of that appear in this columns are added the next day (one row below) if the date in columna DATE (A) if the next day is the consecutive day and only if it is saturday, sunday or monday.
I seems to me that it is working as expected.
Please try it out and let me know.


ShiftBands4.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1Hours worked by bands
2DATESurnameForenameTime InTime OutTotal hoursTo be paid [USD]ABCB2C2BandRateDescriptionFromToMonTueWedThuFriSatSun
32024-01-11 (Thu)Jim09:0020:0011:00230.001:008:002:00--A10.0000:0001:00CCCCCCC
42024-01-12 (Fri)Jim09:0020:007:00130.001:006:00-2:002:00B20.0001:0002:00CCCCCCC
52024-01-13 (Sat)Jim09:0020:0011:00230.001:008:002:002:002:00C30.0002:0003:00CCCCCCC
62024-01-14 (Sun)Jim09:0020:0011:00230.001:008:002:002:002:0003:0004:00CCCCCCC
72024-01-15 (Mon)Jim09:0020:0015:00330.001:0010:004:00--04:0005:00AAAAAAA
82024-01-16 (Tue)Jim09:0020:0011:00230.001:008:002:00--05:0006:00AAAAAAA
906:0007:00AAAAAAA
1007:0008:00AAAAAAA
1108:0009:00AAAAAAA
1209:0010:00AAAAAAA
1310:0011:00BBBBBBB
1411:0012:00BBBBBBB
1512:0013:00BBBBBBB
1613:0014:00BBBBBBB
1714:0015:00BBBBBBB
1815:0016:00BBBBBBB
1916:0017:00BBBBB2B2B2
2017:0018:00BBBBB2B2B2
2118:0019:00CCCCC2C2C2
2219:0020:00CCCCC2C2C2
2320:0021:00CCCCC2C2C2
2421:0022:00CCCCC2C2C2
2522:0023:00CCCCC2C2C2
2623:0024:00CCCCC2C2C2
27
Excel 2019
Cell Formulas
RangeFormula
F3:F8F3=SUM(H3:J3)
G3:G8G3=SUM(($H$2:$J$2=RateBands[Band])*RateBands[Rate]*$H3:$J3)*24
H3:H8,K3:L8H3=IFERROR(SUM((INDEX($T$3:$Z$26, N(IF({1},INT(VALUE(TEXT((($A3+$D3)+(ROW(INDIRECT("A1:A"&ROUND((($A3+$E3+($E3<$D3))-($A3+$D3))/TIME(0,1,0),0)))-1)*TIME(0,1,0)), "hh:mm"))/TIME(1,0,0))+1)), N(IF({1}, WEEKDAY((($A3+$D3)+(ROW(INDIRECT("A1:A"&ROUND((($A3+$E3+($E3<$D3))-($A3+$D3))/TIME(0,1,0),0)))-1)*TIME(0,1,0)),2))))=H$2)*1)*TIME(0,1,0),0)
I3:J8I3=IFERROR(SUM((INDEX($T$3:$Z$26, N(IF({1},INT(VALUE(TEXT((($A3+$D3)+(ROW(INDIRECT("A1:A"&ROUND((($A3+$E3+($E3<$D3))-($A3+$D3))/TIME(0,1,0),0)))-1)*TIME(0,1,0)), "hh:mm"))/TIME(1,0,0))+1)), N(IF({1}, WEEKDAY((($A3+$D3)+(ROW(INDIRECT("A1:A"&ROUND((($A3+$E3+($E3<$D3))-($A3+$D3))/TIME(0,1,0),0)))-1)*TIME(0,1,0)),2))))=I$2)*1)*TIME(0,1,0),0)+IFERROR(($A3-$A2=1)*(WEEKDAY($A3,16)<4)*($A2>0)*K2,0)
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H2:L2,N3:N5,T3:Z26Cell Value="C2"textNO
H2:L2,N3:N5,T3:Z26Cell Value="B2"textNO
H2:L2,N3:N5,T3:Z26Cell Value="C"textNO
H2:L2,N3:N5,T3:Z26Cell Value="B"textNO
H2:L2,N3:N5,T3:Z26Cell Value="A"textNO
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Ordinary Hours or Penalty Shift
Replies
0
Views
163
ChetanPuri
C
K
  • Question
Super Time sheet
Replies
1
Views
280
kolekbolek1283
K
B
  • Solved
Need to add two sets of minutes with custom formatting and round
Replies
1
Views
154
bh24524
B
AOB
  • Question
Calculate the "true" duration of up/down times accounting for gaps and overlaps
Replies
1
Views
349
AOB
AOB
A
  • Solved
Production time calculation within the working day accounting for breaks
2
Replies
16
Views
869
felixstraube
F

Forum statistics

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

Share this page

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