Is there a quick way to create / fill this list

[Dazzybeeguy]

I have a list of 15 people who cover a help desk.

1. Each weekday 2 will cover, 1 person does the 1st part of the day and 1 person covers the next 2 parts of the same day
2. From the example below once person 15 has done their stint it starts at person 1 again.
3. Only 1 person covers on a Saturday and the next person covers the Sunday
4. If a day is a bank holiday no one covers.

Is there a way to easily fill the list of people for a whole year ?

Date	Day	Staff
06-January-25	Monday	Person 1
06-January-25	Monday	person 2
06-January-25	Monday	person 2
07-January-25	Tuesday	Person 3
07-January-25	Tuesday	Person 4
07-January-25	Tuesday	Person 4
08-January-25	Wednesday	Person 5
08-January-25	Wednesday	Person 6
08-January-25	Wednesday	Person 6
09-January-25	Thursday	Person 7
09-January-25	Thursday	Person 8
09-January-25	Thursday	Person 8
10-January-25	Friday	Person 9
10-January-25	Friday	Person 10
10-January-25	Friday	Person 10
11-January-25	Saturday	Person 11
12-January-25	Sunday	Person 12
13-January-25	Monday	Person 13
13-January-25	Monday	Person 14
13-January-25	Monday	Person 14
14-January-25	Tuesday	Person 15
14-January-25	Tuesday	Person 1
14-January-25	Tuesday	Person 1
15-January-25	Wednesday	Person 2
15-January-25	Wednesday	Person 3
15-January-25	Wednesday	Person 3

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F	G	H	I
1	Start Day	# of days	# of people	Holidays			Date	Day/Instance	Person
2	1/1/2025	31	15	1/1/2025	NYD		1/1/2025	Holiday
3				1/20/2025	MLK		1/2/2025	Thursday/1	1
4				2/17/2025	Pres		1/2/2025	Thursday/2	2
5				5/26/2025	Mem		1/2/2025	Thursday/3	2
6							1/3/2025	Friday/1	3
7							1/3/2025	Friday/2	4
8							1/3/2025	Friday/3	4
9							1/4/2025	Saturday/1	5
10							1/5/2025	Sunday/1	6
11							1/6/2025	Monday/1	7
12							1/6/2025	Monday/2	8
13							1/6/2025	Monday/3	8
14							1/7/2025	Tuesday/1	9
15							1/7/2025	Tuesday/2	10
16							1/7/2025	Tuesday/3	10
17							1/8/2025	Wednesday/1	11
18							1/8/2025	Wednesday/2	12
19							1/8/2025	Wednesday/3	12
Sheet8

Cell Formulas
Range	Formula
G2:H74	G2	=LET(start,A2,nd,B2,hol,D2:D12,s,SEQUENCE(nd*4,,start,1/4),m,MOD(s,1),f,FILTER(s,(m=0)+((m<0.75)*(WEEKDAY(s,2)<6)*ISERROR(MATCH(INT(s),hol,0)))),d,IF(ISERROR(MATCH(INT(f),hol,0)),TEXT(f,"dddd")&"/"&MOD(f,1)*4+1,"Holiday"),CHOOSE({1,2},INT(f),d))
I2:I19	I2	=LET(i,RIGHT(H2,1)+0,d,N(XLOOKUP(TRUE,I$1:I1<>"",I$1:I1,,2,-1)),IF(H2="Holiday","",IF(i<3,MOD(d,$C$2)+1,d)))
Dynamic array formulas.

The G2 formula is a Spill formula and fills the G:H columns. The I2 formula has to be dragged down the column. Let us know if this works for you.

 

[Dazzybeeguy]

Try:

Book1
	A	B	C	D	E	F	G	H	I
1	Start Day	# of days	# of people	Holidays			Date	Day/Instance	Person
2	1/1/2025	31	15	1/1/2025	NYD		1/1/2025	Holiday
3				1/20/2025	MLK		1/2/2025	Thursday/1	1
4				2/17/2025	Pres		1/2/2025	Thursday/2	2
5				5/26/2025	Mem		1/2/2025	Thursday/3	2
6							1/3/2025	Friday/1	3
7							1/3/2025	Friday/2	4
8							1/3/2025	Friday/3	4
9							1/4/2025	Saturday/1	5
10							1/5/2025	Sunday/1	6
11							1/6/2025	Monday/1	7
12							1/6/2025	Monday/2	8
13							1/6/2025	Monday/3	8
14							1/7/2025	Tuesday/1	9
15							1/7/2025	Tuesday/2	10
16							1/7/2025	Tuesday/3	10
17							1/8/2025	Wednesday/1	11
18							1/8/2025	Wednesday/2	12
19							1/8/2025	Wednesday/3	12
Sheet8

Cell Formulas
Range	Formula
G2:H74	G2	=LET(start,A2,nd,B2,hol,D2:D12,s,SEQUENCE(nd*4,,start,1/4),m,MOD(s,1),f,FILTER(s,(m=0)+((m<0.75)*(WEEKDAY(s,2)<6)*ISERROR(MATCH(INT(s),hol,0)))),d,IF(ISERROR(MATCH(INT(f),hol,0)),TEXT(f,"dddd")&"/"&MOD(f,1)*4+1,"Holiday"),CHOOSE({1,2},INT(f),d))
I2:I19	I2	=LET(i,RIGHT(H2,1)+0,d,N(XLOOKUP(TRUE,I$1:I1<>"",I$1:I1,,2,-1)),IF(H2="Holiday","",IF(i<3,MOD(d,$C$2)+1,d)))
Dynamic array formulas.

The G2 formula is a Spill formula and fills the G:H columns. The I2 formula has to be dragged down the column. Let us know if this works for you.

That works, Thanks, is there a way when its a holiday on a weekday for it still to have the 3 rows for that day ?

 

[Dazzybeeguy]

That works, Thanks, is there a way when its a holiday on a weekday for it still to have the 3 rows for that day ?

Forget that I worked out how to do it, so thanks

 

[Dazzybeeguy]

That works, Thanks, is there a way when its a holiday on a weekday for it still to have the 3 rows for that day ?

I managed to get it to do the 3 rows for a holiday, however following a holiday it reverts to person 1. What needs to change on the following.

=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)*ISERROR(MATCH(INT(s),"",0)))),d,IF(ISERROR(MATCH(INT(f),hol,0)),TEXT(f,"dddd")&"/"&MOD(f,1)*4+1,"Holiday"),CHOOSE({1,2},INT(f),d))

=LET(i,RIGHT(G2,1)+0,d,N(XLOOKUP(TRUE,H$1:H1<>"",H$1:H1,,2,-1)),IF(G2="Holiday","Bank, Holiday",IF(i<2,MOD(d,$C$2)+1,d)))

 

[Dazzybeeguy]

Try:

Book1
	A	B	C	D	E	F	G	H	I
1	Start Day	# of days	# of people	Holidays			Date	Day/Instance	Person
2	1/1/2025	31	15	1/1/2025	NYD		1/1/2025	Holiday
3				1/20/2025	MLK		1/2/2025	Thursday/1	1
4				2/17/2025	Pres		1/2/2025	Thursday/2	2
5				5/26/2025	Mem		1/2/2025	Thursday/3	2
6							1/3/2025	Friday/1	3
7							1/3/2025	Friday/2	4
8							1/3/2025	Friday/3	4
9							1/4/2025	Saturday/1	5
10							1/5/2025	Sunday/1	6
11							1/6/2025	Monday/1	7
12							1/6/2025	Monday/2	8
13							1/6/2025	Monday/3	8
14							1/7/2025	Tuesday/1	9
15							1/7/2025	Tuesday/2	10
16							1/7/2025	Tuesday/3	10
17							1/8/2025	Wednesday/1	11
18							1/8/2025	Wednesday/2	12
19							1/8/2025	Wednesday/3	12
Sheet8

Cell Formulas
Range	Formula
G2:H74	G2	=LET(start,A2,nd,B2,hol,D2:D12,s,SEQUENCE(nd*4,,start,1/4),m,MOD(s,1),f,FILTER(s,(m=0)+((m<0.75)*(WEEKDAY(s,2)<6)*ISERROR(MATCH(INT(s),hol,0)))),d,IF(ISERROR(MATCH(INT(f),hol,0)),TEXT(f,"dddd")&"/"&MOD(f,1)*4+1,"Holiday"),CHOOSE({1,2},INT(f),d))
I2:I19	I2	=LET(i,RIGHT(H2,1)+0,d,N(XLOOKUP(TRUE,I$1:I1<>"",I$1:I1,,2,-1)),IF(H2="Holiday","",IF(i<3,MOD(d,$C$2)+1,d)))
Dynamic array formulas.

The G2 formula is a Spill formula and fills the G:H columns. The I2 formula has to be dragged down the column. Let us know if this works for you.

I managed to get it to do the 3 rows for a holiday, however following each holiday day it reverts to person 1. What needs to change on the following so if for instance person 10 was the last person before a holiday day that person 11 is shown for the next shift after the holiday. TIA

=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)*ISERROR(MATCH(INT(s),"",0)))),d,IF(ISERROR(MATCH(INT(f),hol,0)),TEXT(f,"dddd")&"/"&MOD(f,1)*4+1,"Holiday"),CHOOSE({1,2},INT(f),d))

=LET(i,RIGHT(G2,1)+0,d,N(XLOOKUP(TRUE,H$1:H1<>"",H$1:H1,,2,-1)),IF(G2="Holiday","Bank, Holiday",IF(i<2,MOD(d,$C$2)+1,d)))

 

[Eric W]

To be honest, I'm not at all sure I understand what you want. Are you saying you want weekday holidays to be in the list, but no one assigned to those days? Or you want weekday holidays on the list, AND someone assigned? If it's the first, try:

Book1
	A	B	C	D	E	F	G	H	I
1	Start Day	# of days	# of people	Holidays			Date	Day/Instance	Person
2	1/1/2025	31	15	1/1/2025	NYD		1/1/2025	Holiday	Bank Holiday
3				1/20/2025	MLK		1/1/2025	Holiday	Bank Holiday
4				2/17/2025	Pres		1/1/2025	Holiday	Bank Holiday
5				5/26/2025	Mem		1/2/2025	Thursday/1	1
6							1/2/2025	Thursday/2	2
7							1/2/2025	Thursday/3	2
8							1/3/2025	Friday/1	3
9							1/3/2025	Friday/2	4
10							1/3/2025	Friday/3	4
11							1/4/2025	Saturday/1	5
12							1/5/2025	Sunday/1	6
46							1/19/2025	Sunday/1	15
47							1/20/2025	Holiday	Bank Holiday
48							1/20/2025	Holiday	Bank Holiday
49							1/20/2025	Holiday	Bank Holiday
50							1/21/2025	Tuesday/1	1
51							1/21/2025	Tuesday/2	2
52							1/21/2025	Tuesday/3	2
Sheet10

Cell Formulas
Range	Formula
G2:H78	G2	=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))
I2:I12,I46:I52	I2	=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)))
Dynamic array formulas.

Notice in the I2 formula that "Bank Holiday" is in the formula twice. Make sure that the spelling and punctuation match for both of them.

If it's the second, you can just remove those holidays from the holiday list. If it's something else, let me know.

 

[Dazzybeeguy]

To be honest, I'm not at all sure I understand what you want. Are you saying you want weekday holidays to be in the list, but no one assigned to those days? Or you want weekday holidays on the list, AND someone assigned? If it's the first, try:

Book1
	A	B	C	D	E	F	G	H	I
1	Start Day	# of days	# of people	Holidays			Date	Day/Instance	Person
2	1/1/2025	31	15	1/1/2025	NYD		1/1/2025	Holiday	Bank Holiday
3				1/20/2025	MLK		1/1/2025	Holiday	Bank Holiday
4				2/17/2025	Pres		1/1/2025	Holiday	Bank Holiday
5				5/26/2025	Mem		1/2/2025	Thursday/1	1
6							1/2/2025	Thursday/2	2
7							1/2/2025	Thursday/3	2
8							1/3/2025	Friday/1	3
9							1/3/2025	Friday/2	4
10							1/3/2025	Friday/3	4
11							1/4/2025	Saturday/1	5
12							1/5/2025	Sunday/1	6
46							1/19/2025	Sunday/1	15
47							1/20/2025	Holiday	Bank Holiday
48							1/20/2025	Holiday	Bank Holiday
49							1/20/2025	Holiday	Bank Holiday
50							1/21/2025	Tuesday/1	1
51							1/21/2025	Tuesday/2	2
52							1/21/2025	Tuesday/3	2
Sheet10

Cell Formulas
Range	Formula
G2:H78	G2	=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))
I2:I12,I46:I52	I2	=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)))
Dynamic array formulas.

Notice in the I2 formula that "Bank Holiday" is in the formula twice. Make sure that the spelling and punctuation match for both of them.

If it's the second, you can just remove those holidays from the holiday list. If it's something else, let me know.

Thanks for the Help, what formula would be best for the following.

Is there a way to divide the shifts up over the year to the teams, so in the example shown there are 7 teams and over the year there are 847 shifts that need allocating fairly so Team B needs 10% so around 84 shifts and so on.

Start Day

# of days

# of people

Holidays

Date

Day/Instance

Person

Team

Team Name

Team Size

%

06/01/2025

365

15

25/12/2024

06/01/2025​

Monday/1

1

Team A

5

12%

26/12/2024

06/01/2025​

Monday/2

2

Team B

4

10%

01/01/2025

06/01/2025​

Monday/3

2

Team C

3

7%

01/04/2025

07/01/2025​

Tuesday/1

3

Team D

9

21%

05/05/2025

07/01/2025​

Tuesday/2

4

Team E

7

17%

26/05/2005

07/01/2025​

Tuesday/3

4

Team F

6

14%

04/08/2025

08/01/2025​

Wednesday/1

5

Team G

8

19%

25/08/2025

08/01/2025​

Wednesday/2

6

42

25/12/2025

08/01/2025​

Wednesday/3

6

26/12/2025

09/01/2025​

Thursday/1

7

01/01/2026

09/01/2025​

Thursday/2

8

06/04/2026

09/01/2025​

Thursday/3

8

04/05/2026

10/01/2025​

Friday/1

9

25/05/2026

10/01/2025​

Friday/2

10

03/08/2026

10/01/2025​

Friday/3

10

31/08/2026

11/01/2025​

Saturday/1

11

25/12/2026

12/01/2025​

Sunday/1

12

26/12/2026

13/01/2025​

Monday/1

13

13/01/2025​

Monday/2

14

13/01/2025​

Monday/3

14

14/01/2025​

Tuesday/1

15

14/01/2025​

Tuesday/2

1

14/01/2025​

Tuesday/3

1

15/01/2025​

Wednesday/1

2

 

[Eric W]

I'm a little confused. The number of people in your list is 15, but there are 42 in your teams? If you want to make sure the teams each have proportionate representation, just set the # of people to 42. It would be easy to create a formula for the team column to show which team each person is on.

 

[Dazzybeeguy]

I'm a little confused. The number of people in your list is 15, but there are 42 in your teams? If you want to make sure the teams each have proportionate representation, just set the # of people to 42. It would be easy to create a formula for the team column to show which team each person is on.

the person column is different people doing different work, the team column will have 15 different teams of different size but for simplicity assume it’s just 7 teams.

I just need need a way to quickly put the list of teams in a list for each shift for the year so they get the correct percentage each and only appear once per day.