Shift bands

Jwinssss · Jan 17, 2024

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,

felixstraube · Jan 21, 2024

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.

Jwinssss · Jan 22, 2024

This would be how the information I export would be

Jwinssss · Jan 22, 2024

felixstraube · Jan 22, 2024

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.

PeteWright · Jan 22, 2024

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

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)

Eric W · Jan 22, 2024

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.

Jwinssss · Jan 25, 2024

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

felixstraube · Jan 25, 2024

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

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

1

Hours worked by bands

2

DATE

Surname

Forename

Time In

Time Out

Total hours

To be paid [USD]

A

B

B-Mon

B-Sat

B-Sun

C

C-Mon

C-Sat

C-Sun

Band

Rate

Description

From

To

Mon

Tue

Wed

Thu

Fri

Sat

Sun

3

2023-09-02 (Sat)

Driver 1

17:15

02:40

9:25

302.00

-

0:45

2:40

-

6:00

A

10.00

00:00

01:00

C

4

2023-09-03 (Sun)

Driver 1

17:15

02:13

8:58

268.25

-

0:45

-

2:13

6:00

-

B

20.00

01:00

02:00

C

5

0:00

0.00

-

C

30.00

02:00

03:00

C

6

0:00

0.00

-

B-Sat

23.00

03:00

04:00

C

7

0:00

0.00

-

B-Sun

24.00

04:00

05:00

A

8

0:00

0.00

-

B-Mon

21.00

05:00

06:00

A

9

0:00

0.00

-

C-Sat

33.00

06:00

07:00

A

10

0:00

0.00

-

C-Sun

34.00

07:00

08:00

A

11

0:00

0.00

-

C-Mon

31.00

08:00

09:00

A

12

0:00

0.00

-

09:00

10:00

A

13

0:00

0.00

-

10:00

11:00

B

14

0:00

0.00

-

11:00

12:00

B

15

0:00

0.00

-

12:00

13:00

B

16

0:00

0.00

-

13:00

14:00

B

17

0:00

0.00

-

14:00

15:00

B

18

0:00

0.00

-

15:00

16:00

B

19

16:00

17:00

B

B-Sat

B-Sun

B-Mon

20

17:00

18:00

B

B-Sat

B-Sun

B-Mon

21

18:00

19:00

C

C-Sat

C-Sun

C-Mon

22

19:00

20:00

C

C-Sat

C-Sun

C-Mon

23

20:00

21:00

C

C-Sat

C-Sun

C-Mon

24

21:00

22:00

C

C-Sat

C-Sun

C-Mon

25

22:00

23:00

C

C-Sat

C-Sun

C-Mon

26

23:00

24:00

C

C-Sat

C-Sun

C-Mon

Sheet1

Cell Formulas
Range	Formula
H2:P2	H2	=TOROW(SORT(RateBands[Band]))
F3:F18	F3	=A3+E3+(E3<D3)-(A3+D3)
G3:G18	G3	=LET( hoursWorkedPerBand, $H3#24, bandsHeader, $H$2#, rates, BYCOL(bandsHeader, LAMBDA(x, XLOOKUP(x, RateBands[Band], RateBands[Rate]))), SUM(hoursWorkedPerBandrates) )
H3:P18	H3	=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
Cell	Condition		Cell Format	Stop If True
H2:P2,R3:R11,X3:AD26	Cell Value	="C-Mon"	text	NO
H2:P2,R3:R11,X3:AD26	Cell Value	="C-Sun"	text	NO
H2:P2,R3:R11,X3:AD26	Cell Value	="B-Mon"	text	NO
H2:P2,R3:R11,X3:AD26	Cell Value	="B-Sun"	text	NO
H2:P2,R3:R11,X3:AD26	Cell Value	="B-Sat"	text	NO
H2:P2,R3:R11,X3:AD26	Cell Value	="C-Sat"	text	NO
H2:P2,R3:R11,X3:AD26	Cell Value	="C"	text	NO
H2:P2,R3:R11,X3:AD26	Cell Value	="B"	text	NO
H2:P2,R3:R11,X3:AD26	Cell Value	="A"	text	NO

Jwinssss · Jan 26, 2024

Hi Felix I have tried to use your sheet but if I change anything the formula changes to #NAME?

Eric W · Jan 26, 2024

The TOROW and BYCOL functions are not available in Excel 2021.

Shift bands

Jwinssss

New Member

felixstraube

Active Member

Excel Facts

Jwinssss

New Member

Jwinssss

New Member

felixstraube

Active Member

PeteWright

Active Member

Eric W

MrExcel MVP

Jwinssss

New Member

felixstraube

Active Member

Jwinssss

New Member

Eric W

MrExcel MVP

Similar threads

Forum statistics

Share this page

Shift bands

New Member

Active Member

Excel Facts

New Member

New Member

Active Member

Active Member

MrExcel MVP

New Member

Active Member

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock