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,
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Welcome to the MrExcel forum!

As you've noticed, this is not an easy problem, as the shortage of responses has indicated. Nevertheless, I think I've figured something out that might work for you. First of all, you say you have 3 bands, but with the end of week exceptions, it's a lot easier to make bands for every day, and on the days where there are end-of-day issues, you might have 4 bands a day.

First open a new workbook. Then build a table like this (you can just copy and paste this if you want, just update the values as needed):

Book1
ABCD
1DayBandStartRate
2SundayC0:001
3A4:002
4B10:003.55
5B216:004
6C18:005
7MondayC4:006
8A10:007
9B18:008
10TuesdayC4:006
11A10:007
12B18:008
13WednesdayC4:006
14A10:007
15B18:008
16ThursdayC4:006
17A10:007
18B18:008
19FridayC4:006
20A10:007
21B16:008
22B218:009
23SaturdayC4:0010
24A10:0011
25B16:0012
26B218:0013
27SundayC0:001
28A4:002
29B10:003
30B216:004
31C18:005
Sheet1


Monday through Thursday has your basic 3 bands, but Friday-Saturday have 4, and Sunday has 5 since it starts the week. There will be a lot of repetition, but it should have all the rates and flexibility you need. Notice that the end time will automatically be the start line on the next row. Also, Sunday is listed twice on purpose, so that the macro is a touch easier to write. Just copy your times and rates.

Now that you have the table, you need to build a table like this with the start/stop times:

Book1
HIJKLM
1DriverStartEndTime Pay Breakdown
2A1/14/2024 12:001/14/2024 21:239:23
3A1/17/2024 8:051/17/2024 19:0310:58
4A1/18/2024 15:001/19/2024 1:2310:23
5B1/15/2024 11:111/15/2024 18:337:22
6B1/22/2024 9:301/22/2024 17:328:02
7C1/15/2024 20:001/16/2024 5:159:15
8C1/18/2024 12:301/18/2024 21:158:45
9C1/19/2024 15:151/20/2024 2:0110:46
Sheet1


The times MUST have both the date and time in the cell!! The formula in K2 just subtracts the start time from the end time to get the total time, displayed as hours:minutes.

Now you need to install the macro. Press Alt-F11 to open the VBA editor. Press Alt-IM to Insert a Module. Then copy the following code to the window that opens:

VBA Code:
Sub GetPay()
Dim MyTable As Range, MyInput As Range, MyTab As Variant, MyData As Variant
Dim lr As Long, r As Long, bd As String, wd As Long, s As Double, e As Double
Dim os As Double, pay As Double, t As Long, w1 As Double, w2 As Double, w3 As Double
Dim dow As String, os2 As Double, wd2 As Long

' Enter the location of the rate table, and the driver start/end table here
    Set MyTable = Sheets("Sheet1").Range("A1:D31")
    Set MyInput = Sheets("Sheet1").Range("H1")
    
' Read the rate table
    MyTab = MyTable.Value
    
' Read the driver table
    lr = MyInput.Offset(100000).End(xlUp).Row
    MyData = MyInput.Offset(1).Resize(lr - MyInput.Row, 6).Value
    
' For each row of the driver table
    For r = 1 To UBound(MyData)
        bd = ""                                                 ' clear the breakdown explanation
        wd = WorksheetFunction.Weekday(MyData(r, 2)) - 1        ' day of week of start time
        s = wd + MyData(r, 2) - Int(MyData(r, 2))               ' adjust the start time to match the rate table
        wd2 = WorksheetFunction.Weekday(MyData(r, 3)) - 1       ' day of week of end time
        If wd = 6 And wd2 = 0 Then wd2 = 7
        e = wd2 + MyData(r, 3) - Int(MyData(r, 3))              ' adjust the end time to match the rate table
        os = 0                                                  ' number of days offset into the table (start)
        os2 = 0                                                 ' number of days offset into the table (end)
        pay = 0                                                 ' clear the pay
        For t = 2 To UBound(MyTab)                              ' check each row of the rate table
            If MyTab(t, 1) <> "" Then dow = MyTab(t, 1)         ' New day of week?
            os = os2
            If MyTab(t + 1, 1) <> "" Then os2 = os2 + 1         ' Next row is a new day?
            
            If MyTab(t, 3) + os > e Then Exit For               ' We're done?
            w1 = IIf(MyTab(t + 1, 3) + os2 < e, MyTab(t + 1, 3) + os2, e)       ' These 3 lines find the amount of time
            w2 = IIf(MyTab(t, 3) + os > s, MyTab(t, 3) + os, s)                 ' that the start/end interval overlaps
            w3 = (w1 - w2) * 24                                                 ' the band interval
            If w3 > 0 Then                                                      ' There is overlap?
                bd = bd & ", " & Format(w3, "#.00") & " hours in " & dow & "/" & MyTab(t, 2) & _
                     " at " & Format(MyTab(t, 4), "$ #.00") & " = " & Format(w3 * MyTab(t, 4), "$ #.00")
                pay = pay + w3 * MyTab(t, 4)                    ' Yes, update the breakdown description and the pay
            End If
        Next t
        MyData(r, 5) = pay                                      ' done with all bands?
        MyData(r, 6) = Mid(bd, 3)                               ' yes, update the results
    Next r
    
    MyInput.Offset(1).Resize(lr - MyInput.Row, 6).Value = MyData       ' Display the results
    
End Sub

Look at the 7th and 8th lines, and update them to have the location in your workbook where you put the tables. Now go back to your Excel window, press Alt-F8 to open the macro selector, select GetPay and click Run. Your table will update like this:

Book1
HIJKLM
1DriverStartEndTime Pay Breakdown
2A1/14/24 12:001/14/24 21:239:23$ 39.124.00 hours in Sunday/B at $ 3.55 = $ 14.20, 2.00 hours in Sunday/B2 at $ 4.00 = $ 8.00, 3.38 hours in Sunday/C at $ 5.00 = $ 16.92
3A1/17/24 8:051/17/24 19:0310:58$ 75.901.92 hours in Wednesday/C at $ 6.00 = $ 11.50, 8.00 hours in Wednesday/A at $ 7.00 = $ 56.00, 1.05 hours in Wednesday/B at $ 8.00 = $ 8.40
4A1/18/24 15:001/19/24 1:2310:23$ 80.073.00 hours in Thursday/A at $ 7.00 = $ 21.00, 7.38 hours in Thursday/B at $ 8.00 = $ 59.07
5B1/15/24 11:111/15/24 18:337:22$ 52.126.82 hours in Monday/A at $ 7.00 = $ 47.72, .55 hours in Monday/B at $ 8.00 = $ 4.40
6B1/22/24 9:301/22/24 17:328:02$ 55.73.50 hours in Monday/C at $ 6.00 = $ 3.00, 7.53 hours in Monday/A at $ 7.00 = $ 52.73
7C1/15/24 20:001/16/24 5:159:15$ 71.508.00 hours in Monday/B at $ 8.00 = $ 64.00, 1.25 hours in Tuesday/C at $ 6.00 = $ 7.50
8C1/18/24 12:301/18/24 21:158:45$ 64.505.50 hours in Thursday/A at $ 7.00 = $ 38.50, 3.25 hours in Thursday/B at $ 8.00 = $ 26.00
9C1/19/24 15:151/20/24 2:0110:46$ 93.40.75 hours in Friday/A at $ 7.00 = $ 5.25, 2.00 hours in Friday/B at $ 8.00 = $ 16.00, 8.02 hours in Friday/B2 at $ 9.00 = $ 72.15
Sheet1


There are things you may want to change, like the hours in column K are in hours/minutes, but the breakdown line has the hours in decimal values. Also, I'd expect that different drivers have different pay rates. So you'd just use the rate table to have the shift differentials, and get the individual rate from somewhere else. Anyway, have a look and let me know what you think.
 
Last edited:
Upvote 0
I do know quite understand how the bands are distributed. Do you have different rates/bands for days from monday to friday and then other rates/bands for saturday and another different for sunday?
Is it something like this?:
1705739353203.png


That means that you actually have 9 different bands like this?:

1705739498219.png


(Adjusting the rate values to the correct values of course, I just entered some values to show something).
 
Upvote 0
I don't understand what you mean by that. Has Monday only one band? Could you create a table like the one i showed with the correct bands and show what rates has each band?
 
Upvote 0
Maybe you need exactly that Eric's solution does. But for me, having read your explanation several times, there is some missing information on how the band are distributed each day and what rates apply for each band. It is not clear to me.
Could you explain in more detail? We will happily come up with a solution once the problem is understood.
 
Upvote 0
I will create a spreadsheet with all the info once I get back to work but

Each day had 3 shift bands A,B & C

So Monday at 04:00 - 10:00 it will be band A
Once it’s reached 10:00 on Monday they will will then be getting paid in band B and if they work till 18:00 they get paid band C

As I said I will try get a sheet together so it makes more sense
 
Upvote 0
Ok here is what I came up with:
First of all, at the moment I only have the excel web app so I can't past XL2BB code, sorry for that. But I'll upload the working file so you can test it.

1705783455741.png


In the table called "RateBands" (O2:Q8), you define the different bands. And the corresponding rates. You can edit this table as needed, adding more bands/rates or deleting existing ones.
In the cells S3:Y26 you need to define what band applies to each hour for each day.
Then in column B and C you enter start and end datetimes and in columna E-J you will get the worked hour for each band. And in column D you will get the total amount you have to pay the driver (hours worked for each band multiplied by the rate of each band, and then all summed up).
Note: If you want to add more than 4 more Band/Rates to the table you will have to add more columns to right of column M for example so the formula won't spill over the "RateBands" table.

Here is the file to download:

ShiftBands.zip

The formulas used in E2 is:
Excel Formula:
=TOROW(SORT(RateBands[Band]))

The formula in E3 is (and copied down):
Excel Formula:
=LET(start, B3, end, C3,
bands, $U$3:$AA$26,
bandsHeader, $E$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
)

The formula in D3 is (and copied down):
Excel Formula:
=LET(
hoursWorkedPerBand, $E3#*24,
bandsHeader, $E$2#,
rates, BYCOL(bandsHeader, LAMBDA(x, XLOOKUP(x, RateBands[Band], RateBands[Rate]))),
SUM(hoursWorkedPerBand*rates)
)

Accuracy of time calculation is to the minute. I guess that is enough.
Let me know if this works for you or if we need to make some modifications.
 
Last edited:
Upvote 0
When I get a chance to test it I will get back to you but can I just say you are all magnificent. how do you learn this I’m a novice and would love to develop
 
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