Looking for a way to allocate shifts between teams, maybe a LET formula

Dazzybeeguy

Board Regular
Joined
Jan 6, 2022
Messages
118
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
What formula would work in J2:J888 to split the 845 or so shifts over the year to Teams A to Team G, so team B would get around 84 shifts and Team G double that as the teams are of a different size so each team gets a percentage number of shifts based on their size.

They would need to be spaced out fairly and a team would only cover on one of the shifts per day.

Weekdays there are 3 shifts
Weekends only 1 shift
None on a National Holiday

The year start is in Cell A2 and its the 6th January 2025, column J2:J888 needs the formula to split the shifts, K L & M has the info re the 7 team names and sizes / relative percentage based on the number of staff in the teams.

The Formula in G2 is =LET(start,A2,nd,B2,hol,$D$2:$D$20,s,SEQUENCE(nd*4,,start,1/4),m,MOD(s,1),f,FILTER(s,(m=0)+((m<0.75)*(WEEKDAY(s,2)<6))),d,IF(ISERROR(MATCH(INT(f),hol,0)),TEXT(f,"dddd")&"/"&MOD(f,1)*4+1,"Holiday"),CHOOSE({1,2},INT(f),d))

The formula is I2 is =LET(i,RIGHT(H2,1)+0,d,N(XLOOKUP(TRUE,I$1:I1<>"Bank, Holiday",I$1:I1,,0,-1)),IF(H2="Holiday","Bank, Holiday",IF(i<3,MOD(d,$C$2)+1,d)))

Could a LET formula be the answer ?


A B C D EF G H I J K L M
Start Day# of days# of peopleHolidaysDateDay/ShiftPersonTeamTeam NameTeam Size%
06/01/20253651525/12/2024
06/01/2025
Monday/11Team A512%
26/12/2024
06/01/2025
Monday/22Team B410%
01/01/2025
06/01/2025
Monday/32Team C37%
01/04/2025
07/01/2025
Tuesday/13Team D921%
05/05/2025
07/01/2025
Tuesday/24Team E717%
26/05/2005
07/01/2025
Tuesday/34Team F614%
04/08/2025
08/01/2025
Wednesday/15Team G819%
25/08/2025
08/01/2025
Wednesday/2642
25/12/2025
08/01/2025
Wednesday/36
26/12/2025
09/01/2025
Thursday/17
01/01/2026
09/01/2025
Thursday/28
06/04/2026
09/01/2025
Thursday/38
04/05/2026
10/01/2025
Friday/19
25/05/2026
10/01/2025
Friday/210
03/08/2026
10/01/2025
Friday/310
31/08/2026
11/01/2025
Saturday/111
25/12/2026
12/01/2025
Sunday/112
26/12/2026
13/01/2025
Monday/113
13/01/2025
Monday/214
13/01/2025
Monday/314
14/01/2025
Tuesday/115
14/01/2025
Tuesday/21
14/01/2025
Tuesday/31
15/01/2025
Wednesday/12
15/01/2025
Wednesday/23
15/01/2025
Wednesday/33
16/01/2025
Thursday/14
16/01/2025
Thursday/25
16/01/2025
Thursday/35
17/01/2025
Friday/16
17/01/2025
Friday/27
17/01/2025
Friday/37
18/01/2025
Saturday/18
19/01/2025
Sunday/19
20/01/2025
Monday/110
20/01/2025
Monday/211
20/01/2025
Monday/311
21/01/2025
Tuesday/112
21/01/2025
Tuesday/213
21/01/2025
Tuesday/313
22/01/2025
Wednesday/114
22/01/2025
Wednesday/215
22/01/2025
Wednesday/315
23/01/2025
Thursday/11
23/01/2025
Thursday/22
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Forum statistics

Threads
1,224,541
Messages
6,179,418
Members
452,912
Latest member
alicemil

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