Nested If-Filter Formula

[Austin Lang]

Hey all,

I have been staring at this project as a whole for several days, and I need some help with this part. I cannot seem to see what is wrong with my formula. What I have is a cleaning log. As things are cleaned, they are entered into worksheet. In a helper cell on the first sheet, I have it doing a concatenate to match wash date and start time. I then use those helper cells on a 2nd tab. On this separate tab, I am using this nested formula to filter out the cleanings that took place on a specific shift:

=IFERROR(IF(BJ3="1st Shift",FILTER(Form!A5:BH3000,(Form!BI5:BI3000>BO15)*(Form!BI5:BI3000<BO16)),IF(BJ3="2nd Shift",FILTER(Form!A5:BH3000,(Form!BI5:BI3000>BO16)*(Form!BI5:BI3000<BO17)),IF(AND(BO5>BP17,BO5<BO7),FILTER(Form!A5:BH3000,(Form!BI5:BI3000>BO17)*(Form!BI5:BI3000<BO8)),FILTER(Form!A5:BH3000,(Form!BI5:BI3000>BO18)*(Form!BI5:BI3000<BO15))))),"")

For whatever reason, I am not getting the correct information returned. This formula is in cell B10. The red box is what should be returning. (Blue boxes covering unnecessary information for solving the issue. In this instance, I am looking for items that are started after 18:00 and 06:00 the next morning. The last two statements were the workaround I found to make it worked based on whether or not it was before or after 00:00.

Where am I going wrong with this?

Thank you in advance.

 

[KRice]

I'm confused now because earlier you mentioned something about using start times, but I don't have enough information to offer specific guidance...I haven't seen anything about the Form worksheet that clarifies its structure or location so the ranges I referenced may not be correct.

I'm assuming there are no issues with the right side block for describing the start times for the 4 shifts that overlap (even partially) with the "Current Date", as well as the last moment of the Current Date before the clock rolls over to "tomorrow". This same information block also contains a formula (BO4) that incorporates the expression BO1+BO2-BO3, which subtracts the reporting grace period from the current date/time to give us a moment in time that defines what shift we can report on. If all of this looks okay, then let's examine how the larger list on the Form job log is filtered by the formula in B10:

=FILTER(Form!A5:BH3000,IFERROR(MATCH(Form!X5:X3000+Form!Y5:Y3000,BO7#,1),0)=MATCH(BO1+BO2-BO3,BO7#,1),"none")

Breaking this down...
MATCH(BO1+BO2-BO3,BO7#,1)
...tells us the list position in our dates/times block (BO7#) for the current reporting date/time (i.e., does our current time +15 minute grace period correspond to the 3rd shift that began yesterday, today's 1st shift, etc.). Any time entered in BO2 and any reasonably small reporting grace period in BO3 must produce a match somewhere in BO7#, so we'll see returned values of 1,2,3, or 4 from MATCH, and then we use the CHOOSE function to convert these numbers to something more intuitive to us: displaying 3, 1, 2, or "wait until end of shift" to represent actual shift numbers or for the case where the reporting time is 22:15 or later (assuming a 15 minute reporting grace period), since these times are nowhere close to the end-of-shift time, we generate the "wait" message.

We use a similar date/time categorization method for the jobs logged on the Form worksheet...
MATCH(Form!X5:X3000+Form!Y5:Y3000,BO7#,1)
...tells us the list position in our dates/times block (BO7#) for each job logged on worksheet Form. Previously, I assumed that Form!X5:X3000 represents the date the job was started and Form!Y5:Y3000 represents the time the job was started---because I thought you wanted the jobs categorized by start time. If this understanding is incorrect, then change these references to the ranges that contain the appropriate dates and times. It sounds as if you want the time column to refer to the "End" time rather than the "Start" time, but I don't know where that information is found. In my revised notional work log table on the Forms worksheet (shown below), I've clarified whether the dates/times represent start or end. And then a slightly revised B10 formula references the End Date and End Time ranges on the Form worksheet.

Going back to the MATCH function just described, we do need to wrap this formula in an IFERROR function because MATCH will return an error if MATCH can't find a match, and we replace those errors with 0's. We then FILTER the range of interest on the Form worksheet, requiring only that the two MATCH functions return the same number.

MrExcel_20230625.xlsx
	A	B	C	D																				X	Y	Z																																			BI
4	Equipment	CIP Skid	Status of CIP	Type of Clean																				CIP End Date	CIP Start Time	CIP End Time																																			CIP End Date/Time
5	A	x	f	h																				6/18/2023	8:00	11:00																																			06/18/2023 11:00
6	B	c	g	k																				6/18/2023	11:00	16:00																																			06/18/2023 16:00
7	A1	v	Aborted (Maintenance)	Re-Clean (Idle)																				6/19/2023	19:32	20:08																																			06/19/2023 20:08
8	B1	b	Clean	Allergen																				6/19/2023	20:00	20:15																																			06/19/2023 20:15
9	C1	n	Aborted (Maintenance)	Re-Clean (Idle)																				6/19/2023	20:30	21:03																																			06/19/2023 21:03
10	D1	m	Clean	Allergen																				6/20/2023	21:01	0:11																																			06/20/2023 00:11
11	C	k	t	y																				6/19/2023	5:00	7:41																																			06/19/2023 07:41
12	E1	n	Clean	Re-Clean (Idle)																				6/19/2023	23:30	4:23																																			06/19/2023 04:23
13	F1	p	Aborted (Maintenance)	Re-Clean (Idle)																				6/19/2023	23:42	1:02																																			06/19/2023 01:02
Form

Cell Formulas
Range	Formula
BI5:BI13	BI5	=X5+Z5

MrExcel_20230625.xlsx
	B	C	D	E																				Y	Z	AA																																					BL	BM	BN	BO
1																																																																	Current Date:	6/19/2023
2																																																																	Current Time:	6:14
3																																																																	Reporting Window (h:mm):	0:15
4																																																																	Shift Reported on:	3
5
6																																																																# entries	Description of time category	Date/Time Thresholds
7																																																																1	1. 3rd shift "day prior" begins	6/18/2023 22:00:00
8																																																																0	2. 1st shift "day of" begins	6/19/2023 6:00:00
9	Equipment	CIP Skid	Status of CIP	Type of Clean																				CIP End Date	CIP Start Time	CIP End Time																																						3	3. 2nd shift "day of" begins	6/19/2023 14:00:00
10	E1	n	Clean	Re-Clean (Idle)																				6/19/2023	23:30	4:23																																						-	4. 3rd shift "day of" begins	6/19/2023 22:00:00
11	F1	p	Aborted (Maintenance)	Re-Clean (Idle)																				6/19/2023	23:42	1:02																																						-	5. last moment "day of" 3rd shift	6/19/2023 23:59:59
2ndTab

Cell Formulas
Range	Formula
BO4	BO4	=CHOOSE(MATCH(BO1+BO2-BO3,BO7#,1),3,1,2,"wait until end of shift")
BO7:BO11	BO7	=BO1-{1;0;0;0;0}+{22;6;14;22;24}/24-{0;0;0;0;1}/60/60/24
BM7:BM9	BM7	=SUMPRODUCT(--(IFERROR(MATCH(Form!$X$5:$X$3000+Form!$Y$5:$Y$3000,BO$7#,1),0)=ROWS($7:7)))
B10:BI11	B10	=FILTER(Form!A5:BH3000,IFERROR(MATCH(Form!X5:X3000+Form!Z5:Z3000,BO7#,1),0)=MATCH(BO1+BO2-BO3,BO7#,1),"none")
Dynamic array formulas.

 

[Austin Lang]

Sorry for the delayed response.

I created this document a few years ago and have been building on it ever since. That column began as a "Start Date" and I have called it that ever since. I never thought that what I labeled that column was wrong and it should be end date. A failure on my part. Also, your Form example is accurate. The missing columns are concentration setpoints and actual results.

With that being said, the revision that you made to accommodate the end time was the fix this needed. It works with no issue now.

Thank you so much for your continued responses and working through this with me. It is greatly appreciated.

 

[KRice]

Excellent! I’m happy to help. Thank you for clarifying details about the table.