Shift bands

[Jwinssss]

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,

 

[Jwinssss]

B

 

[Eric W]

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
	A	B	C	D
1	Day	Band	Start	Rate
2	Sunday	C	0:00	1
3		A	4:00	2
4		B	10:00	3.55
5		B2	16:00	4
6		C	18:00	5
7	Monday	C	4:00	6
8		A	10:00	7
9		B	18:00	8
10	Tuesday	C	4:00	6
11		A	10:00	7
12		B	18:00	8
13	Wednesday	C	4:00	6
14		A	10:00	7
15		B	18:00	8
16	Thursday	C	4:00	6
17		A	10:00	7
18		B	18:00	8
19	Friday	C	4:00	6
20		A	10:00	7
21		B	16:00	8
22		B2	18:00	9
23	Saturday	C	4:00	10
24		A	10:00	11
25		B	16:00	12
26		B2	18:00	13
27	Sunday	C	0:00	1
28		A	4:00	2
29		B	10:00	3
30		B2	16:00	4
31		C	18:00	5
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
	H	I	J	K	L	M
1	Driver	Start	End	Time	Pay	Breakdown
2	A	1/14/2024 12:00	1/14/2024 21:23	9:23
3	A	1/17/2024 8:05	1/17/2024 19:03	10:58
4	A	1/18/2024 15:00	1/19/2024 1:23	10:23
5	B	1/15/2024 11:11	1/15/2024 18:33	7:22
6	B	1/22/2024 9:30	1/22/2024 17:32	8:02
7	C	1/15/2024 20:00	1/16/2024 5:15	9:15
8	C	1/18/2024 12:30	1/18/2024 21:15	8:45
9	C	1/19/2024 15:15	1/20/2024 2:01	10: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:

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
	H	I	J	K	L	M
1	Driver	Start	End	Time	Pay	Breakdown
2	A	1/14/24 12:00	1/14/24 21:23	9:23	$ 39.12	4.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
3	A	1/17/24 8:05	1/17/24 19:03	10:58	$ 75.90	1.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
4	A	1/18/24 15:00	1/19/24 1:23	10:23	$ 80.07	3.00 hours in Thursday/A at $ 7.00 = $ 21.00, 7.38 hours in Thursday/B at $ 8.00 = $ 59.07
5	B	1/15/24 11:11	1/15/24 18:33	7:22	$ 52.12	6.82 hours in Monday/A at $ 7.00 = $ 47.72, .55 hours in Monday/B at $ 8.00 = $ 4.40
6	B	1/22/24 9:30	1/22/24 17:32	8:02	$ 55.73	.50 hours in Monday/C at $ 6.00 = $ 3.00, 7.53 hours in Monday/A at $ 7.00 = $ 52.73
7	C	1/15/24 20:00	1/16/24 5:15	9:15	$ 71.50	8.00 hours in Monday/B at $ 8.00 = $ 64.00, 1.25 hours in Tuesday/C at $ 6.00 = $ 7.50
8	C	1/18/24 12:30	1/18/24 21:15	8:45	$ 64.50	5.50 hours in Thursday/A at $ 7.00 = $ 38.50, 3.25 hours in Thursday/B at $ 8.00 = $ 26.00
9	C	1/19/24 15:15	1/20/24 2:01	10: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.

 

[felixstraube]

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?:

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

(Adjusting the rate values to the correct values of course, I just entered some values to show something).

 

[kvsrinivasamurthy]

Sunday from 16:00 - 18:00 it’s Monday rate.
Same as above.

Does this continue for all days of week.

 

[felixstraube]

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?

 

[felixstraube]

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.

 

[Jwinssss]

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

 

[felixstraube]

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.

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:

=TOROW(SORT(RateBands[Band]))

The formula in E3 is (and copied down):

=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):

=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.

 

[Jwinssss]

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