Weekly rotating names in calendar year

[Lindap7575]

This may be way over my excel knowledge, but I have 13 names that I need to rotate in a calendar year - our weeks start on Friday and end on Friday. I honestly don't know where or how to begin, and I have been searching and reading and can't find a simple tool to create this? Is there something that can create this rotating schedule? Technically each person shouldn't have to be on call 1 x per 13 weeks, but due to holidays it gets adjusted, so the same people do not have the holiday year after year. Is there a simple tutorial I should be looking up to do this? Thank you!

 

[KRice]

Welcome to the MrExcel board!
A few questions: Your weeks begin on Fridays, but are weekends to be considered? Which days are considered weekends? (assuming Saturday and Sunday, but would like to confirm)
It sounds as if this is an "on call" schedule. Does that mean you are looking to assign only one name for each day to consider?
For weeks near end of year and beginning of year, to which "year" does that week belong if the week includes New Year's Day?

 

[Lindap7575]

Welcome to the MrExcel board!
A few questions: Your weeks begin on Fridays, but are weekends to be considered? Which days are considered weekends? (assuming Saturday and Sunday, but would like to confirm)
It sounds as if this is an "on call" schedule. Does that mean you are looking to assign only one name for each day to consider?
For weeks near end of year and beginning of year, to which "year" does that week belong if the week includes New Year's Day?

Hi - so it's an on call schedule for physicians, they start at 5pm on Friday and end at 8am the following Friday. So for this week the week starts 11/3 @ 5pm and will end 11/10 @ 8am. So going into 2024 my first week would be 1/5 through 1/12- so i just want to have all the weeks listed and the names just auto populate rotating and not just copy and paste continuously through the year.

 

[KRice]

Check to see if something like this will work. Input the year of interest in cell B1 and add to the Names table the staff who will be assigned (in the order they are to be assigned). The single formula should then "spill" (using Excel 365) the results for the entire year.

MrExcel_20231030_B.xlsx
	A	B	C	D
1	Year	2023		Names
2				name1
3	Date			name2
4	Friday, January 6, 2023	name1		name3
5	Friday, January 13, 2023	name2		name4
6	Friday, January 20, 2023	name3
7	Friday, January 27, 2023	name4
8	Friday, February 3, 2023	name1
9	Friday, February 10, 2023	name2
10	Friday, February 17, 2023	name3
11	Friday, February 24, 2023	name4
12	Friday, March 3, 2023	name1
13	Friday, March 10, 2023	name2
14	Friday, March 17, 2023	name3
15	Friday, March 24, 2023	name4
16	Friday, March 31, 2023	name1
Sheet2

Cell Formulas
Range	Formula
A4:B55	A4	=LET(names,Table2[Names],wkary,SEQUENCE(QUOTIENT(DAYS(CEILING(DATE($B$1+1,1,1)-6,7)+6,CEILING(DATE($B$1,1,1)-6,7)+6)+1,7),,CEILING(DATE($B$1,1,1)-6,7)+6,7),nary,INDEX(names,MOD(SEQUENCE(ROWS(wkary),,0),COUNTA(names))+1),HSTACK(wkary,nary))
Dynamic array formulas.

 

[KRice]

If you want to do this every year, then the formula can be simplified a bit by replacing the portion that calculates the number of weeks between two dates with 52...like this:

=LET(names,Table2[Names],wkary,SEQUENCE(52,,CEILING(DATE($B$1,1,1)-6,7)+6,7),nary,INDEX(names,MOD(SEQUENCE(ROWS(wkary),,0),COUNTA(names))+1),HSTACK(wkary,nary))

 

[Luu]

If you want to do this every year, then the formula can be simplified a bit by replacing the portion that calculates the number of weeks between two dates with 52...like this:

=LET(names,Table2[Names],wkary,SEQUENCE(52,,CEILING(DATE($B$1,1,1)-6,7)+6,7),nary,INDEX(names,MOD(SEQUENCE(ROWS(wkary),,0),COUNTA(names))+1),HSTACK(wkary,nary))

Hello, can this same formula be modified to include only weekends and holidays? Can you help me with the formula, please?

 

[KRice]

Can you tell me more, please? The solution above shows the Friday start date for every week in the calendar year.

1. See my questions in post #2...are you considering Saturday and Sunday as weekends, or something else?
2. Are you looking for a list of list of single dates...each row containing only one date that falls on either a weekend or a holiday? Or do you want the entire weekend represented by a single cell? In other words, for the year, are you anticipating about 52 rows of weekend dates (each row representing Saturday-Sunday presumably), or 104 rows of a weekend days?
3. Do you have any rules for what constitutes a "holiday"? For example, if a traditional holiday falls on a weekend day, is the holiday to be ignored, or is it celebrated (for your scheduling purposes) on the day before or the day after the weekend?

 

[Luu]

Hi KRice, thank you for getting back to me so quickly.
1. I want to consider weekends (Saturday and Sunday) plus the Holidays (listed below) as 'working' days and assign employees to work on the specified weekends and holidays.
2. I want each weekend day and holiday represented by a single cell with the corresponding employee in the adjacent column.
3. I was able to include weekends using your formula but I don't know how to include holidays as well. The holidays I listed below are all on weekdays.
4. One other separate but related question, is there a way to randomize but not cluster which employee is assigned to work so that one employee is not working two weekends in a row for example?

1

 

[KRice]

Your approach should work fine...almost there. The easiest adjustment is to rename the weekend dates array as "weary"...only to avoid potential confusion with the earlier formula. Then add an array for the holiday (to work) dates (I've called it "hary", and this holidays array refers to a range...you could also reference the Holidays table in your worksheet). Then stack those two arrays together vertically and sort them (see the SORT(VSTACK construction) to create an "all days array"...which I named "adary". This gives you all the Saturdays, Sundays, and Holiday dates in a single vertical array. Then the names array "nary" is formed by considering the new overall length of adary, and the INDEX/MOD/SEQUENCE construction gives a repeating sequence of your Names list (this is one way to do it). Finally, those two arrays (adary and nary) are stacked horizontally and returned by the formula.

The only issue may be to confirm whether your boundaries are correct...is the first date to appear always in the calendar year shown in A1? Is it conceivable the the last date to appear might be in the early days of the subsequent year?

MrExcel_20231030_B.xlsx
	A	B	C	D	E				I
1	2024			Names					List of Holidays
2	Monday, January 1, 2024	name1		name1					Monday, January 1, 2024
3	Tuesday, January 2, 2024	name2		name2					Tuesday, January 2, 2024
4	Saturday, January 6, 2024	name3		name3					Monday, January 15, 2024
5	Sunday, January 7, 2024	name4		name4					Monday, February 19, 2024
6	Saturday, January 13, 2024	name5		name5					Friday, April 5, 2024
7	Sunday, January 14, 2024	name6		name6					Monday, May 27, 2024
8	Monday, January 15, 2024	name7		name7					Thursday, July 4, 2024
9	Saturday, January 20, 2024	name8		name8					Friday, August 9, 2024
10	Sunday, January 21, 2024	name1							Monday, September 2, 2024
11	Saturday, January 27, 2024	name2							Thursday, November 28, 2024
12	Sunday, January 28, 2024	name3							Friday, November 29, 2024
13	Saturday, February 3, 2024	name4							Tuesday, December 24, 2024
14	Sunday, February 4, 2024	name5							Wednesday, December 25, 2024
15	Saturday, February 10, 2024	name6							Tuesday, December 31, 2024
16	Sunday, February 11, 2024	name7
17	Saturday, February 17, 2024	name8
Luu_WE-HOL

Cell Formulas
Range	Formula
A2:B119	A2	=LET(names,Table25[Names],weary,WORKDAY.INTL(DATE($A$1,1,1),SEQUENCE(104),"1111100"),hary,I2:I15,adary,SORT(VSTACK(weary,hary)),nary,INDEX(names,MOD(SEQUENCE(ROWS(adary),,0),COUNTA(names))+1),HSTACK(adary,nary))
Dynamic array formulas.

 

[Luu]

Dear Kirk, Just Brilliant!
Yes, and yes to both of your questions. I would like the beginning date to start with Jan of the previous year and the last date to go into January of the subsequent year.