Need help with scheduling formulas

[jordan89m]

I work at a hotel and we have scheduled crews staying with us. In an effort to better assign crew members to rooms with as little unoccupied time between stays AND also maintaining at least a given amount of time between stays for cleaning I've been trying to work out a rooming schedule.

I'll try to break it down:

in a tab titled 'days' is a table with all records of the dataset.

Book.xlsx
	A	B	C	D	E	F	G
1	arrdate	arrtime	depdate	deptime	record#	Column6	Column7
2	01/08/22	7:28	03/08/22	16:25	10361	50FEXXHX	16:25
3	01/08/22	21:10	02/08/22	22:00	9669	50TEDDHX	22:00
4	01/08/22	21:10	02/08/22	22:00	9670	50TEDDHX	22:00
5	01/08/22	21:31	02/08/22	22:05	9667	50TEDDHX	22:05
6	01/08/22	21:31	02/08/22	22:05	9668	50TEDDHX	22:05
7	02/08/22	6:10	04/08/22	5:41	13698	50MEXXHX	05:41
8	02/08/22	6:10	04/08/22	5:41	13699	50MEXXHX	05:41
9	02/08/22	7:28	04/08/22	16:25	10362	50FEXXHX	16:25
10	02/08/22	7:28	04/08/22	16:25	10363	50FEXXHX	16:25
11	02/08/22	11:39	03/08/22	5:35	9675	50TEDDHX	05:35
12	02/08/22	11:39	03/08/22	5:35	9676	50TEDDHX	05:35
13	02/08/22	12:45	03/08/22	7:15	9677	50TEDDHX	07:15
14	02/08/22	12:45	03/08/22	7:15	9678	50TEDDHX	07:15
days

Cell Formulas
Range	Formula
G2:G14	G2	=days!$D2

in the tab titled buffer there is one cell indicating a cutoff time in which a room is no longer cleanable on a given day, and below it, all known departure times in column A and the ideal end of cleaning times in column B.

Book.xlsx
	A	B	C
4	max
5	20:50
6
7	deps	window
8	16:25	17:55
9	22:00	23:30
10	22:05	23:35
11	05:41	07:11
12	05:35	07:05
13	07:15	08:45
14	11:05	12:35
15	15:40	17:10
16	12:55	14:25
17	15:05	16:35
18	12:35	14:05
19	21:35	23:05
20	13:55	15:25
21	21:40	23:10
22	20:20	21:50
23	11:00	12:30
24	07:25	08:55
25	06:45	08:15
26	05:05	06:35
27	17:40	19:10
28	00:00	01:30
buffer

Cell Formulas
Range	Formula
A8:A28	A8	=IFERROR(UNIQUE(days!$D$2:$D$1400),"")
B8:B28	B8	=$A8+1.5/24
Press CTRL+SHIFT+ENTER to enter array formulas.

then in the tab titled main, room numbers are populated down column A

Book.xlsx
	A	B	C	D
1		30-Jul
2	701
3	702
4	716
5	717
6	718
7	719
8	720
9	721
10	801
11	802
12	816
13	817
main

Right of the rooms is where data for each day is populated. As you can see, 4 crew members departing after the cutoff time on 2-aug trigger a "-20" flag (this is internal jargon, but basically means the room is not usable until the next day.) Record numbers from the 'days' tab skip rows every time a -20 is present. This is a must.

Book.xlsx
	H	I	J	K	L	M
1	01-Aug			02-Aug
2	10361	16:25		13698	05:41
3	9669	22:00				-20
4	9670	22:00				-20
5	9667	22:05				-20
6	9668	22:05				-20
7				13699	05:41
8				10362	16:25
9				10363	16:25
10				9675	05:35
11				9676	05:35
12				9677	07:15
13				9678	07:15
main

Cell Formulas
Range	Formula
H2:H13,K2:K13	H2	=IF(J2="-20","",IFERROR(INDEX(days!$A$2:$E$1400,SMALL(IF(ISNUMBER(MATCH(days!$A$2:$A$1400,H$1,0)),MATCH(ROW(days!$A$2:$A$1400),ROW(days!$A$2:$A$1400)),""),ROWS(H$1:H1)-COUNTIF(J$2:J2,"-20")),5),""))
I2:I13,L2:L13	I2	=IFERROR(VLOOKUP(H2,days!$E$2:$G$1400,3,FALSE),"")
J2:J13,M2:M13	J2	=IFERROR(IF(VLOOKUP(E2,days!$E$2:$G$1400,3,FALSE)>buffer!$A$5,"-20",""),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Now for the part I'm stuck on...
I need to maintain the skipping rows where -20 is present but also now only assign records from the days tab if their arrival time is equal to or greater than the buffer time from the previous crew occupying the room. If it is not, the next room would be tested by a formula, basically distributing all expected crew on a given date to various rooms.

Looking forward to seeing what you geniuses put together! This forum has taught me much over the years, though I've only just registered in light of my new dilemma.

I'm sure more clarification will be needed and I'll be checking back constantly to do so!

Cheers,
Jordan