Find n number of consecutive zeros between two dates

[Newcook]

I have a schedule to find available rooms for a number of people on work rotations from a number of possible rooms. I was able to add all of the occupancies in rows 124 to 128 where each row represents the occupancy of a particular room, so zeros indicate available space in each room. I found this formula from another thread: =ADDRESS(ROW(O$124),COLUMN(O$124)-1+FIND(REPT(1,$A$149),CONCAT(IF($O$124:$JF$128=0,1,0))),4) that gives me the first instance of n number of consecutive zeros in O124:JF128, however, I am looking to find the first instance but between a start date in B149 and end date in C149. n is in A149, the dates are in O4:JF4. Once I have the first instance, from the row number I can identify which room to put a person in.

 

[Eric W]

This is a recap of the discussion in another thread. With the dates like so:

Book2
	A	B												N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
4															1/11/2021	1/18/2021	1/25/2021	2/1/2021	2/8/2021	2/15/2021	2/22/2021	3/1/2021	3/8/2021	3/15/2021	3/22/2021	3/29/2021	4/5/2021	4/12/2021	4/19/2021	4/26/2021	5/3/2021	5/10/2021
Sheet4

And the rest of the range like this:

Book2
	A	B												N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
124															1	2	3	0	1	4	6	0	0	0	0	0	0	0	0	1	2	9
125															4	5	7	0	0	0	0	0	6	2	1	8	9	33	0	0	0	0
126															77	88	0	0	0	0	0	0	0	1	2	3	4	5	6	8	0	0
127															0	0	0	1	2	3	0	9	0	9	7	8	0	0	0	0	0	0
128	Length	Start												End	8	0	8	0	0	0	0	8	8	8	8	8	8	8	8	8	8	8
149	7	2/1/2021												4/1/2021
150
151	Cell:	$Q$126
Sheet4

Cell Formulas
Range	Formula
B151	B151	=ADDRESS(ROW(O124)-1+MATCH(MIN(IFERROR(FIND(REPT("0",A149),MID(CONCAT(IF(O124:JF128=0,"0",".")),SEQUENCE(ROWS(O124:JF128),,1,COLUMNS(O124:JF128)),COLUMNS(O124:JF128))),"")),IFERROR(FIND(REPT("0",A149),MID(CONCAT(IF(O124:JF128=0,"0",".")),SEQUENCE(ROWS(O124:JF128),,1,COLUMNS(O124:JF128)),COLUMNS(O124:JF128))),""),0),COLUMN(O124)-1+MIN(IFERROR(FIND(REPT("0",A149),MID(CONCAT(IF(O124:JF128=0,"0",".")),SEQUENCE(ROWS(O124:JF128),,1,COLUMNS(O124:JF128)),COLUMNS(O124:JF128))),"")))

The B151 formula should solve the problem, except for the matching the date part. I also offered this version of the formula using LET:

=LET(rng,O124:JF128,r,ROWS(rng),c,COLUMNS(rng),tl,INDEX(rng,1,1),fa,IFERROR(FIND(REPT("0",A149),MID(CONCAT(IF(rng=0,"0",".")),SEQUENCE(r,,1,c),c)),""),ADDRESS(ROW(tl)-1+MATCH(MIN(fa),fa,0),COLUMN(tl)-1+MIN(fa)))

Full disclosure, I don't actually have LET to test with, so that's a bit theoretical. But if my LET logic is sound, then this version:

=LET(rng,INDEX(O124:JF124,MATCH(B149,O4:JF4)):JF128,r,ROWS(rng),c,COLUMNS(rng),tl,INDEX(rng,1,1),fa,IFERROR(FIND(REPT("0",A149),MID(CONCAT(IF(rng=0,"0",".")),SEQUENCE(r,,1,c),c)),""),ADDRESS(ROW(tl)-1+MATCH(MIN(fa),fa,0),COLUMN(tl)-1+MIN(fa)))

should handle the date requirement too. It'll take a lot more work to retrofit that change into the original formula, but I'll look at that when I have the change, if the LET is not successful.

 

[Newcook]

Wow, thank you Eric, I tried assigning a room with your LET formula above and it was right on. I'll have to try with a few other rooms to be sure, but it looks like it will work. I'll post again later when I've tried a few more.

 

[Newcook]

I just assigned 16 rooms in a matter on minutes. the thing that took the longest is trying to remember which room number each line represents. I've actually extended it to 20 rooms because that is how many we have. I had to make a screenshot of what row belongs to which room because I couldn't remember them once I got past the first five, ha,ha. But it worked absolutely perfectly for all 16 rooms. You have no idea how much time you just saved me. Thank you so much.

 

[Eric W]

Woohoo! Glad it works for you. That was actually kinda fun to figure out. (Also a shout out to @Peter_SSs since his basic idea is at the core of the function.)

the thing that took the longest is trying to remember which room number each line represents

I wondered about that. Instead of returning the row number, you could put the room number in column N (for example), and have the formula return the room number? And maybe the date instead of the column number? Or do you need the cell address so you can go to that spot and update the cells to show they're now assigned? I wonder if HYPERLINK works in LET, so you can just click on it to go to that cell.

 

[Newcook]

I really only need the room number, and the house name. I could add the room number next to each row. I already have a formula that takes care of the rest. There are 5 rooms per house, so the first 5 rows (124 to 128) belong to one house, the next 5 rows (129 to 133) to another, etc.

 

[Newcook]

So I entered the row number in JH124 by using the ROW() function, then in JI124 the house name and in JK124, the room number, and so on for all the rows. Then I did an index and match which works well but I'm not sure how to get the row from the let formula. I tried making another column with RIGHT function for last three digits, but my index and match didn't like that.

 

[Eric W]

Try using RIGHT(xxx,3)+0 to get the last three digits. If you just use RIGHT, the result will be a text value, and the MATCH function will not match that with a numeric value. When you add 0 to a number stored as text, it coerces it back to a numeric value, which should match ok.

 

[Newcook]

Yay, that worked. Thank you. I was able to assign 36 rooms this morning by hand typing the three digits. That would normally have taken me hours to do. Have a little over a hundred to do this season. Will be great to start with this next season.