Dazzybeeguy
Board Regular
- Joined
- Jan 6, 2022
- Messages
- 118
- Office Version
- 365
- 2010
- Platform
- 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
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
|
