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
 
Sort by date Sort by votes
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...
What would the formulas be if I want bank holidays to be treated the same as a weekend, so it would only have 1 entry for that day and would allocate to the next person same as a weekend day.

Thanks
 
Upvote 0

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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...
Hi, what I want is for the bank holidays to be treated as a one shift day and someone allocated to that shift (they would be on call)
 
Upvote 0
That would be an easy change. One thought, given that on regular weekdays there are 2 people assigned, 1 for the 1st part, and 1 for the next 2 parts, would it be ok if I bundled parts 2 and 3 into 1 line, like Tuesday/2/3 ? That would simplify things, but I can still do it the other way if you want.

Also, for the teams, are they assigned the same way? Two for regular weekdays, one for weekends and holidays? And you just want them assigned proportionately to their sizes, but order doesn't matter?
 
Upvote 0
Eric W said:
That would be an easy change. One thought, given that on regular weekdays there are 2 people assigned, 1 for the 1st part, and 1 for the next 2 parts, would it be ok if I bundled parts 2 and 3 into 1 line, like Tuesday/2/3 ? That would simplify things, but I can still do it the other way if you want.

Also, for the teams, are they assigned the same way? Two for regular weekdays, one for weekends and holidays? And you just want them assigned proportionately to their sizes, but order doesn't matter?
Click to expand...
I want each Holiday day to show 1 line the same as a Saturday and to assign that holiday day to the next person I will try and upload an image
 
Last edited:
Upvote 0
Eric W said:
That would be an easy change. One thought, given that on regular weekdays there are 2 people assigned, 1 for the 1st part, and 1 for the next 2 parts, would it be ok if I bundled parts 2 and 3 into 1 line, like Tuesday/2/3 ? That would simplify things, but I can still do it the other way if you want.

Also, for the teams, are they assigned the same way? Two for regular weekdays, one for weekends and holidays? And you just want them assigned proportionately to their sizes, but order doesn't matter?
Click to expand...
 

Attachments

  • IMG_4407.jpeg
    IMG_4407.jpeg
    156.1 KB · Views: 4
Upvote 0
As per the image I just want 1 line for a holiday and the next number being assigned it, so one person for that day, maybe instead of saying Monday/1 etc it could say the Monday/BH.

From the generated number I can use Xlookup to find that number and to put the name of the person in next column.

Thanks for your help, i am going to try and work out all the elements of your formula, its a bit confusing at my current level of expertise.
 
Last edited:
Upvote 0
Let help.xlsx
ABCDEFGHIJKLM
1Start Day# of days# of peopleHolidaysDateDay/InstancePerson NoPerson NameNumberName List
223/12/20243651525/12/2024Xmas 23/12/2024Monday/11Ant, adam, 1Ant, adam,
326/12/2024Boxing23/12/2024Monday/22Bassett, Bertie2Bassett, Bertie
401/01/2025New year’s day 202523/12/2024Monday/32Bassett, Bertie3Chaplin, Charlie
501/04/2025Easter Monday 202524/12/2024Tuesday/13Chaplin, Charlie4Day, Darren
605/05/2025Early May Bank Holiday 202524/12/2024Tuesday/24Day, Darren5Eastwood, Ernie
726/05/2025Spring Bank Holiday 202524/12/2024Tuesday/34Day, Darren6Flintstone, Fred
804/08/2025Summer Bank Holiday 202525/12/2024HolidayBank, HolidayBank, Holiday7Gee, Whiz
925/08/2025Late Summer Bank Holiday 202526/12/2024HolidayBank, HolidayBank, Holiday8Houdini, Harry
1025/12/2025Christmas Day 202527/12/2024Friday/15Eastwood, Ernie9Itch, Ivor
1126/12/2025Boxing Day 202527/12/2024Friday/26Flintstone, Fred10Jackson, Janet
1201/01/2026New year’s day 202627/12/2024Friday/36Flintstone, Fred11Kiki, Kurt
1306/04/2026Easter Monday 202628/12/2024Saturday/17Gee, Whiz12Luca, Lord
1404/05/2026Early May Bank Holiday 202629/12/2024Sunday/18Houdini, Harry13Miles, Many
1525/05/2026Spring Bank Holiday 202630/12/2024Monday/19Itch, Ivor14Not, Now
1603/08/2026Summer Bank Holiday 202630/12/2024Monday/210Jackson, Janet15Oprey, old
1731/08/2026Late Summer Bank Holiday 202630/12/2024Monday/310Jackson, JanetBank, HolidayBank, Holiday
1825/12/2026Christmas Day 202631/12/2024Tuesday/111Kiki, Kurt
1926/12/2026Boxing Day 202631/12/2024Tuesday/212Luca, Lord
2031/12/2024Tuesday/312Luca, Lord
2101/01/2025HolidayBank, HolidayBank, Holiday
2202/01/2025Thursday/113Miles, Many
2302/01/2025Thursday/214Not, Now
2402/01/2025Thursday/314Not, Now
2503/01/2025Friday/115Oprey, old
2603/01/2025Friday/21Ant, adam,
2703/01/2025Friday/31Ant, adam,
2804/01/2025Saturday/12Bassett, Bertie
2905/01/2025Sunday/13Chaplin, Charlie
Sheet1
Cell Formulas
RangeFormula
G2:H872G2=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:I29I2=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)))
J2:J29J2=XLOOKUP(I2,$L$2:$L$17,$M$2:$M$17,"",FALSE)
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
K1:K17Cell Valuecontains "Bank Holidays"textNO
K3:K17Expression=$I3=$I2textNO
K1:K17Cell Valuecontains "baldo"textNO
K1:K17Cell Valuecontains "bank, holiday"textNO
D2:D19Expression=$G3=0textNO
D2:D19Expression=$G3>0textNO
 
Upvote 0
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Assign people to work shifts, considering holidays
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
My latest stab at your requirements:

Book1
ABCDEFGHIJKLMNOPQRSTU
1Start Day# of days# of peopleHolidaysDateDay/InstancePerson NoPerson NameTeamNumberName ListTeam NameTeam Size%CountIncremental CountActual
212/23/20243651512/25/2024Xmas 12/23/2024Monday/11Ant, adam, C1Ant, adam, A512%73.5714373.571428674
312/26/2024Boxing12/23/2024Monday/2/32Bassett, BertieB2Bassett, BertieB410%58.85714132.42857159
41/1/2025New year’s day 202512/24/2024Tuesday/13Chaplin, CharlieD3Chaplin, CharlieC37%44.14286176.57142944
54/1/2025Easter Monday 202512/24/2024Tuesday/2/34Day, DarrenE4Day, DarrenD921%132.4286309132
65/5/2025Early May Bank Holiday 202512/25/2024Xmas /BH5Eastwood, ErnieD5Eastwood, ErnieE717%103412103
75/26/2025Spring Bank Holiday 202512/26/2024Boxing/BH6Flintstone, FredG6Flintstone, FredF614%88.28571500.28571489
88/4/2025Summer Bank Holiday 202512/27/2024Friday/17Gee, WhizF7Gee, WhizG819%117.7143618117
98/25/2025Late Summer Bank Holiday 202512/27/2024Friday/2/38Houdini, HarryA8Houdini, HarryTotal42
1012/25/2025Christmas Day 202512/28/2024Saturday/19Itch, IvorG9Itch, Ivor
1112/26/2025Boxing Day 202512/29/2024Sunday/110Jackson, JanetD10Jackson, Janet
121/1/2026New year’s day 202612/30/2024Monday/111Kiki, KurtD11Kiki, Kurt
134/6/2026Easter Monday 202612/30/2024Monday/2/312Luca, LordC12Luca, Lord
145/4/2026Early May Bank Holiday 202612/31/2024Tuesday/113Miles, ManyA13Miles, Many
155/25/2026Spring Bank Holiday 202612/31/2024Tuesday/2/314Not, NowE14Not, Now
168/3/2026Summer Bank Holiday 20261/1/2025New year’s day 2025/BH15Oprey, oldF15Oprey, old
178/31/2026Late Summer Bank Holiday 20261/2/2025Thursday/11Ant, adam, GBank, HolidayBank, Holiday
1812/25/2026Christmas Day 20261/2/2025Thursday/2/32Bassett, BertieF
1912/26/2026Boxing Day 20261/3/2025Friday/13Chaplin, CharlieG
201/3/2025Friday/2/34Day, DarrenD
211/4/2025Saturday/15Eastwood, ErnieG
221/5/2025Sunday/16Flintstone, FredG
231/6/2025Monday/17Gee, WhizD
241/6/2025Monday/2/38Houdini, HarryF
251/7/2025Tuesday/19Itch, IvorF
261/7/2025Tuesday/2/310Jackson, JanetD
271/8/2025Wednesday/111Kiki, KurtF
281/8/2025Wednesday/2/312Luca, LordA
291/9/2025Thursday/113Miles, ManyA
Sheet11
Cell Formulas
RangeFormula
G2:H619G2=LET(start,A2,nd,B2,hol,D2:D20,hold,E2:E20,s,SEQUENCE(nd*2,,start,1/2),m,MOD(s,1),f,FILTER(s,(m=0)+((WEEKDAY(s,2)<6)*ISERROR(MATCH(INT(s),hol,0)))),d,IFERROR(XLOOKUP(INT(f),hol,hold,,0),TEXT(f,"dddd"))&"/"&IFS(ISNUMBER(MATCH(INT(f),hol,0)),"BH",MOD(f,1)=0,"1",1,"2/3"),CHOOSE({1,2},INT(f),d))
J2:J619J2=XLOOKUP(I2:INDEX(I:I,ROWS(G2#)+1),M2:M16,N2:N16,"",0)
K2:K619K2=LET(a,ROWS(G2#),m,IFERROR(MATCH(SEQUENCE(a,,0),T1:T8),1),INDEX(P2:P8,SORTBY(m,RANDARRAY(a))))
S2:S8S2=ROWS(G$2#)*R2:R8
U2:U8U2=COUNTIF(K:K,P2:P8)
R2:R8R2=Q2/Q$9
T2:T8T2=ROWS(G$2#)*SUM(R$2:R2)
Q9Q9=SUM(Q2:Q8)
I2:I29I2=MOD(N(I1),C$2)+1
Dynamic array formulas.
 
Upvote 0
Solution
Dazzybeeguy said:
I want each Holiday day to show 1 line the same as a Saturday and to assign that holiday day to the next person I will try and upload an image
Click to expand...

Eric W said:
My latest stab at your requirements:

Book1
ABCDEFGHIJKLMNOPQRSTU
1Start Day# of days# of peopleHolidaysDateDay/InstancePerson NoPerson NameTeamNumberName ListTeam NameTeam Size%CountIncremental CountActual
212/23/20243651512/25/2024Xmas 12/23/2024Monday/11Ant, adam, C1Ant, adam, A512%73.5714373.571428674
312/26/2024Boxing12/23/2024Monday/2/32Bassett, BertieB2Bassett, BertieB410%58.85714132.42857159
41/1/2025New year’s day 202512/24/2024Tuesday/13Chaplin, CharlieD3Chaplin, CharlieC37%44.14286176.57142944
54/1/2025Easter Monday 202512/24/2024Tuesday/2/34Day, DarrenE4Day, DarrenD921%132.4286309132
65/5/2025Early May Bank Holiday 202512/25/2024Xmas /BH5Eastwood, ErnieD5Eastwood, ErnieE717%103412103
75/26/2025Spring Bank Holiday 202512/26/2024Boxing/BH6Flintstone, FredG6Flintstone, FredF614%88.28571500.28571489
88/4/2025Summer Bank Holiday 202512/27/2024Friday/17Gee, WhizF7Gee, WhizG819%117.7143618117
98/25/2025Late Summer Bank Holiday 202512/27/2024Friday/2/38Houdini, HarryA8Houdini, HarryTotal42
1012/25/2025Christmas Day 202512/28/2024Saturday/19Itch, IvorG9Itch, Ivor
1112/26/2025Boxing Day 202512/29/2024Sunday/110Jackson, JanetD10Jackson, Janet
121/1/2026New year’s day 202612/30/2024Monday/111Kiki, KurtD11Kiki, Kurt
134/6/2026Easter Monday 202612/30/2024Monday/2/312Luca, LordC12Luca, Lord
145/4/2026Early May Bank Holiday 202612/31/2024Tuesday/113Miles, ManyA13Miles, Many
155/25/2026Spring Bank Holiday 202612/31/2024Tuesday/2/314Not, NowE14Not, Now
168/3/2026Summer Bank Holiday 20261/1/2025New year’s day 2025/BH15Oprey, oldF15Oprey, old
178/31/2026Late Summer Bank Holiday 20261/2/2025Thursday/11Ant, adam, GBank, HolidayBank, Holiday
1812/25/2026Christmas Day 20261/2/2025Thursday/2/32Bassett, BertieF
1912/26/2026Boxing Day 20261/3/2025Friday/13Chaplin, CharlieG
201/3/2025Friday/2/34Day, DarrenD
211/4/2025Saturday/15Eastwood, ErnieG
221/5/2025Sunday/16Flintstone, FredG
231/6/2025Monday/17Gee, WhizD
241/6/2025Monday/2/38Houdini, HarryF
251/7/2025Tuesday/19Itch, IvorF
261/7/2025Tuesday/2/310Jackson, JanetD
271/8/2025Wednesday/111Kiki, KurtF
281/8/2025Wednesday/2/312Luca, LordA
291/9/2025Thursday/113Miles, ManyA
Sheet11
Cell Formulas
RangeFormula
G2:H619G2=LET(start,A2,nd,B2,hol,D2:D20,hold,E2:E20,s,SEQUENCE(nd*2,,start,1/2),m,MOD(s,1),f,FILTER(s,(m=0)+((WEEKDAY(s,2)<6)*ISERROR(MATCH(INT(s),hol,0)))),d,IFERROR(XLOOKUP(INT(f),hol,hold,,0),TEXT(f,"dddd"))&"/"&IFS(ISNUMBER(MATCH(INT(f),hol,0)),"BH",MOD(f,1)=0,"1",1,"2/3"),CHOOSE({1,2},INT(f),d))
J2:J619J2=XLOOKUP(I2:INDEX(I:I,ROWS(G2#)+1),M2:M16,N2:N16,"",0)
K2:K619K2=LET(a,ROWS(G2#),m,IFERROR(MATCH(SEQUENCE(a,,0),T1:T8),1),INDEX(P2:P8,SORTBY(m,RANDARRAY(a))))
S2:S8S2=ROWS(G$2#)*R2:R8
U2:U8U2=COUNTIF(K:K,P2:P8)
R2:R8R2=Q2/Q$9
T2:T8T2=ROWS(G$2#)*SUM(R$2:R2)
Q9Q9=SUM(Q2:Q8)
I2:I29I2=MOD(N(I1),C$2)+1
Dynamic array formulas.
Click to expand...

Thanks for the help, that does the job perfectly.

If you ever have the time and inclination I would really appreciate a brief breakdown of the two LET formulas & the MOD formula so that I might understand what each part is doing.

=LET(start,A2,nd,B2,hol,D2:D20,hold,E2:E20,s,SEQUENCE(nd*2,,start,1/2),m,MOD(s,1),f,FILTER(s,(m=0)+((WEEKDAY(s,2)<6)*ISERROR(MATCH(INT(s),hol,0)))),d,IFERROR(XLOOKUP(INT(f),hol,hold,,0),TEXT(f,"dddd"))&"/"&IFS(ISNUMBER(MATCH(INT(f),hol,0)),"BH",MOD(f,1)=0,"1",1,"2/3"),CHOOSE({1,2},INT(f),d))

=LET(a,ROWS(G2#),m,IFERROR(MATCH(SEQUENCE(a,,0),T1:T8),1),INDEX(P2:P8,SORTBY(m,RANDARRAY(a))))

=MOD(N(I1),C$2)+1
 
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