Is there a quick way to create / fill this list

D

Dazzybeeguy

Board Regular
Joined
Jan 6, 2022
Messages
111
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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 ?
DateDayStaff
06-January-25MondayPerson 1
06-January-25Mondayperson 2
06-January-25Mondayperson 2
07-January-25TuesdayPerson 3
07-January-25TuesdayPerson 4
07-January-25TuesdayPerson 4
08-January-25WednesdayPerson 5
08-January-25WednesdayPerson 6
08-January-25WednesdayPerson 6
09-January-25ThursdayPerson 7
09-January-25ThursdayPerson 8
09-January-25ThursdayPerson 8
10-January-25FridayPerson 9
10-January-25FridayPerson 10
10-January-25FridayPerson 10
11-January-25SaturdayPerson 11
12-January-25SundayPerson 12
13-January-25MondayPerson 13
13-January-25MondayPerson 14
13-January-25MondayPerson 14
14-January-25TuesdayPerson 15
14-January-25TuesdayPerson 1
14-January-25TuesdayPerson 1
15-January-25WednesdayPerson 2
15-January-25WednesdayPerson 3
15-January-25WednesdayPerson 3
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
Try:

Book1
ABCDEFGHI
1Start Day# of days# of peopleHolidaysDateDay/InstancePerson
21/1/202531151/1/2025NYD1/1/2025Holiday 
31/20/2025MLK1/2/2025Thursday/11
42/17/2025Pres1/2/2025Thursday/22
55/26/2025Mem1/2/2025Thursday/32
61/3/2025Friday/13
71/3/2025Friday/24
81/3/2025Friday/34
91/4/2025Saturday/15
101/5/2025Sunday/16
111/6/2025Monday/17
121/6/2025Monday/28
131/6/2025Monday/38
141/7/2025Tuesday/19
151/7/2025Tuesday/210
161/7/2025Tuesday/310
171/8/2025Wednesday/111
181/8/2025Wednesday/212
191/8/2025Wednesday/312
Sheet8
Cell Formulas
RangeFormula
G2:H74G2=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:I19I2=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.
 
Upvote 0
Eric W said:
Try:

Book1
ABCDEFGHI
1Start Day# of days# of peopleHolidaysDateDay/InstancePerson
21/1/202531151/1/2025NYD1/1/2025Holiday 
31/20/2025MLK1/2/2025Thursday/11
42/17/2025Pres1/2/2025Thursday/22
55/26/2025Mem1/2/2025Thursday/32
61/3/2025Friday/13
71/3/2025Friday/24
81/3/2025Friday/34
91/4/2025Saturday/15
101/5/2025Sunday/16
111/6/2025Monday/17
121/6/2025Monday/28
131/6/2025Monday/38
141/7/2025Tuesday/19
151/7/2025Tuesday/210
161/7/2025Tuesday/310
171/8/2025Wednesday/111
181/8/2025Wednesday/212
191/8/2025Wednesday/312
Sheet8
Cell Formulas
RangeFormula
G2:H74G2=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:I19I2=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.
Click to expand...
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 ?
 
Upvote 0
Dazzybeeguy said:
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 ?
Click to expand...
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)))
 
Upvote 0
Eric W said:
Try:

Book1
ABCDEFGHI
1Start Day# of days# of peopleHolidaysDateDay/InstancePerson
21/1/202531151/1/2025NYD1/1/2025Holiday 
31/20/2025MLK1/2/2025Thursday/11
42/17/2025Pres1/2/2025Thursday/22
55/26/2025Mem1/2/2025Thursday/32
61/3/2025Friday/13
71/3/2025Friday/24
81/3/2025Friday/34
91/4/2025Saturday/15
101/5/2025Sunday/16
111/6/2025Monday/17
121/6/2025Monday/28
131/6/2025Monday/38
141/7/2025Tuesday/19
151/7/2025Tuesday/210
161/7/2025Tuesday/310
171/8/2025Wednesday/111
181/8/2025Wednesday/212
191/8/2025Wednesday/312
Sheet8
Cell Formulas
RangeFormula
G2:H74G2=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:I19I2=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.
Click to expand...
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)))
 
Last edited:
Upvote 0
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
ABCDEFGHI
1Start Day# of days# of peopleHolidaysDateDay/InstancePerson
21/1/202531151/1/2025NYD1/1/2025HolidayBank Holiday
31/20/2025MLK1/1/2025HolidayBank Holiday
42/17/2025Pres1/1/2025HolidayBank Holiday
55/26/2025Mem1/2/2025Thursday/11
61/2/2025Thursday/22
71/2/2025Thursday/32
81/3/2025Friday/13
91/3/2025Friday/24
101/3/2025Friday/34
111/4/2025Saturday/15
121/5/2025Sunday/16
461/19/2025Sunday/115
471/20/2025HolidayBank Holiday
481/20/2025HolidayBank Holiday
491/20/2025HolidayBank Holiday
501/21/2025Tuesday/11
511/21/2025Tuesday/22
521/21/2025Tuesday/32
Sheet10
Cell Formulas
RangeFormula
G2:H78G2=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:I52I2=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.
 
Upvote 0
Eric W said:
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
ABCDEFGHI
1Start Day# of days# of peopleHolidaysDateDay/InstancePerson
21/1/202531151/1/2025NYD1/1/2025HolidayBank Holiday
31/20/2025MLK1/1/2025HolidayBank Holiday
42/17/2025Pres1/1/2025HolidayBank Holiday
55/26/2025Mem1/2/2025Thursday/11
61/2/2025Thursday/22
71/2/2025Thursday/32
81/3/2025Friday/13
91/3/2025Friday/24
101/3/2025Friday/34
111/4/2025Saturday/15
121/5/2025Sunday/16
461/19/2025Sunday/115
471/20/2025HolidayBank Holiday
481/20/2025HolidayBank Holiday
491/20/2025HolidayBank Holiday
501/21/2025Tuesday/11
511/21/2025Tuesday/22
521/21/2025Tuesday/32
Sheet10
Cell Formulas
RangeFormula
G2:H78G2=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:I52I2=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.
Click to expand...
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 peopleHolidaysDateDay/InstancePersonTeamTeam 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
 
Upvote 0
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.
 
Upvote 0
Eric W said:
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.
Click to expand...
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Formula to create a list based
Replies
2
Views
87
myall_blues
myall_blues
S
  • Solved
NETWORKDAYS.INTL
Replies
7
Views
92
Fluff
Fluff
N
  • Question Question
Help w/ Excel Formula
Replies
4
Views
124
JGordon11
JGordon11
S
  • Solved
Calculate Backdated dates
Replies
5
Views
266
Joe4
Joe4
J
  • Question Question
Formula for weekdays of date in another cell
Replies
4
Views
1K
Jaxi
J

Forum statistics

Threads
1,222,795
Messages
6,168,280
Members
452,175
Latest member
Nalini1998

Share this page

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