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]

Very little in this post makes sense. You have a "Current Date" of 6/20/2022 (cell BO3) and the records you describe that should be returned have CIP dates in calendar year 2023.

In this instance, I am looking for items that are started after 18:00 and 06:00 the next morning.

Where is this established? Nothing in the formula or in the worksheet image shows these times. Do you mean to say having a CIP Date and Time occurring during the "3rd Shift" (selected in cell BJ3) for the "Current Date" (entered in BO3)?

What are your shift thresholds?...Does 1st Shift begin at 06:00 or 06:15? Does 2nd Shift begin at 14:00 or 14:15? Does 3rd Shift begin at 22:00 or 22:15? Presumably 3rd Shift spans across two dates. If you are interested in returning records for 3rd Shift, do you want only those items whose CIP Date/Time occur late during the same calendar day as the date indicated in BO3...or do you also want the items whose CIP Date/Time occur very early the following morning (still during the 3rd Shift that began the day prior)?

One issue I see is that your AND function is comparing a time to a date/time combination. So I suspect you want:

AND(BO3+BO5>BP17,BO3+BO5<BO8)

 

[Austin Lang]

First of all, Thank you for taking the time to look this over as much as you did. I was doing this at 3am my time, and caffeine was not plentiful.. I have a feeling that is what caused a lot of my issues.

The fact that I had 2022 was 75% of my issue. I didn't even notice that until you mentioned it.

The shift start times are at 6:00, 14:00, and 22:00. I had them set to xx:15 to give them a grace period to send out the report before it switches over. Mentioning the times that I did was just a mistake on my part. We have recently changed our reporting from two twelve-hour shifts to three eight-hour shifts. I guess I am having a hard time breaking old habits. 3rd shift does indeed span across two shifts and that right there causes a lot of problems in my formula. Well, it did. I am looking for CIPs that were conducted during the shift. For night shift, I would be looking for 6/19 22:15- 6/20 06:15.

I will also make the change to the formula that you suggested.

Thanks again for your assistance.

 

[KRice]

Thanks for clarifying. I'm still a bit puzzled by two things:
1. You have a Current Time field and a Shift field. Are they related in any way? For example, if you put 6/20 in the Current Date couldn't you select either 1st, 2nd, or 3rd Shift, regardless of what the Current Time is? Or are you using the Current Time to automatically determine which shift is currently working?...so a current time of 5:55 AM is nearing the end of 3rd Shift that began the night before?

2. Related to the first question, I can't determine whether the objective is to be able to return historical data for any given date/shift combination or if it is intended to be used to return work logged only recently. Suppose it is 11:30 PM (Current Time) on 6/20 (Current Date). That falls within 3rd Shift. The first question above aims to understand which of these two really matter (Current Time or Shift shown in BJ3). Now I am wondering what should be returned for 3rd Shift. Do you want the activities logged for 6/19 22:15 - 6/20 06:15 (the most recent completed 3rd Shift) or any very recently activities logged for 6/20 22:15 to the present time of 6/20 23:30...or both?

 

[KRice]

To offer some context for my questions, I've assumed your log sheet called Form resembles this:

MrExcel_20230625.xlsx
	C	D																				X	Y	Z																																			BI
4	Status of CIP	Type of Clean																				CIP Date	CIP Start Time	CIP End Time																																			CIP Start Date/Time
5	f	h																				6/18/2023	8:00	11:00																																			45095.333
6	g	k																				6/18/2023	11:00	16:00																																			45095.458
7	Aborted (Maintenance)	Re-Clean (Idle)																				6/19/2023	19:32	20:08																																			45096.814
8	Clean	Allergen																				6/19/2023	20:00	20:15																																			45096.833
9	Aborted (Maintenance)	Re-Clean (Idle)																				6/19/2023	20:30	21:03																																			45096.854
10	Clean	Allergen																				6/20/2023	21:01	0:11																																			45097.876
11	t	y																				6/19/2023	5:00	7:41																																			45096.208
Form

Cell Formulas
Range	Formula
BI5:BI11	BI5	=X5+Y5

An alternative approach eliminates the IF statements and instead relies on dynamic arrays to create the date/time categories of interest and then categorizes all of the Form entries to determine which, if any, fall into the defined date/time categories of interest (those Shifts immediately around the Current Date in BO3). The user then selects the Shift of interest in BJ3 and the results are reported. A separate count is made in BM7 and down showing the number of logged entries in each of the date/time categories.

MrExcel_20230625.xlsx
	B	C	D	E																				Y	Z	AA																																			BJ		BL	BM	BN	BO
2																																																													Select Shift					Current Date:
3																																																													2nd Shift					6/19/2023
4																																																																		Current Time:
5																																																																	not used -->	5:55
6																																																																# entries	Description of time category	Date/Time Thresholds
7																																																																1	1. 3rd shift day prior begins	6/18/2023 22:15:00
8																																																																0	2. 1st shift day of	6/19/2023 6:15:00
9	Equipment	CIP Skid	Status of CIP	Type of Clean																				CIP Date	CIP Start Time	CIP End Time																																						3	3. 2nd shift day of	6/19/2023 14:15:00
10	A1	v	Aborted (Maintenance)	Re-Clean (Idle)																				6/19/2023	19:32	20:08																																						0	4. 3rd shift day of begins	6/19/2023 22:15:00
11	B1	b	Clean	Allergen																				6/19/2023	20:00	20:15																																						1	5. last moment of 3rd shift day of before spilling into next day	6/20/2023 0:14:59
12	C1	n	Aborted (Maintenance)	Re-Clean (Idle)																				6/19/2023	20:30	21:03
13
2ndTab

Cell Formulas
Range	Formula
BO7:BO11	BO7	=BO3-{1;0;0;0;0}+{22;6;14;22;24}/24+15/60/24-{0;0;0;0;1}/24/60/60
B10:BI12	B10	=FILTER(Form!A5:BH3000,IFERROR(XMATCH(Form!X5:X3000+Form!Y5:Y3000,BO7#,-1),0)=XLOOKUP(BJ3,{"1st Shift";"2nd Shift";"3rd Shift dp";"3rd Shift do"},{2;3;1;4}),"none")
BM7:BM11	BM7	=SUMPRODUCT(--(IFERROR(XMATCH(Form!$X$5:$X$3000+Form!$Y$5:$Y$3000,BO$7#,-1),0)=ROWS($7:7)))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
BJ3	List	1st Shift,2nd Shift,3rd Shift dp, 3rd Shift do

Depending on what is to be reported for the 3rd shifts (whether you want only those log entries that belong to the day prior's 3rd shift--that also spills forward into the start of the current day--or you want those that belong to the 3rd Shift that begins late in the current day, or you want both categories) may require some additional adjustments to the formula. Neither the helper column in Form!BI nor the Current Time in BO5 is used.

 

[Austin Lang]

Wow, lots of information!

For the first post you made..
1. The goal of this page is to make it completely automated and need no interaction. All of the cells on the right are going to be hidden as they are only helper cells. The current date and time is supposed to be just simple TODAY and NOW formulas. For the sake of the testing, I have removed the date and time and put in my own. I have months of data to play with so I was entering different time and dates to make sure it worked as it should. The last half of your statement is correct. I am looking for it to automatically determine the shift.
2. What this is going to be used for is for a Technician to email of the CIPs that have been conducted during their shift. That is why I have a 15 minute window at the end. Around or shortly after the end of their shift, they need to go in and copy and paste this information. This is not going to be used for historical research. The form this is reading can be filtered if we are looking at historical.

For the 2nd post you made..
The form does look like that. With a lot more conditional formatting and crap. Lol.

I am more than willing to change the formula all-together and use this instead. The only thing that I want to call out is that this file is accessed by Microsoft Teams (which I loathe), and I have run into instances where certain formulas where this becomes a problem. With what you provided, I don't see that problem arising, but I like to call it out.

 

[KRice]

Thanks for the additional information...that helps a lot and it cleared up how to approach the time-of-day/shift question I asked about. Have a look at the following. You interact mainly with the yellow-shaded cells to set the date, time, and reporting grace period (expressed as hours:minutes...h:mm). One formula constructs the date/time starts for three shifts beginning with the previous day's 3rd shift, extending through the 2nd shift of the current day, and then additional array elements mark the beginning and end of 3rd shift during the current day (which will not be used for reporting on the "current" day...those would be accounted for on the next day). Based on the these shift definitions, another formula uses the current date/time to establish the shift to be reported on, accounting for the reporting grace period. The implication of this approach is that a report must be made before the grace period expires, otherwise, the formula rolls over to the next shift. A third formula filters the data log to extract those records corresponding to the date/time/shift. Finally, a fourth formula is used for information purposes/debugging to provide a count of the number of entries corresponding to the three shifts of interest to aid in manually setting some times to confirm that the correct entries are returned.

MrExcel_20230625.xlsx
	B	C	D	E																				Y	Z	AA																																					BL	BM	BN	BO
1																																																																	Current Date:	6/19/2023
2																																																																	Current Time:	16:15
3																																																																	Reporting Window (h:mm):	0:15
4																																																																	Shift Reported on:	2
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 Date	CIP Start Time	CIP End Time																																						3	3. 2nd shift "day of" begins	6/19/2023 14:00:00
10	A1	v	Aborted (Maintenance)	Re-Clean (Idle)																				6/19/2023	19:32	20:08																																						-	4. 3rd shift "day of" begins	6/19/2023 22:00:00
11	B1	b	Clean	Allergen																				6/19/2023	20:00	20:15																																						-	5. last moment "day of" 3rd shift	6/19/2023 23:59:59
12	C1	n	Aborted (Maintenance)	Re-Clean (Idle)																				6/19/2023	20:30	21:03
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:BI12	B10	=FILTER(Form!A5:BH3000,IFERROR(MATCH(Form!X5:X3000+Form!Y5:Y3000,BO7#,1),0)=MATCH(BO1+BO2-BO3,BO7#,1),"none")
Dynamic array formulas.

 

[Austin Lang]

All of this looks beautiful, however there is an issue that I didn't account for.

As you would, CIP Date is the date in which the CIP has taken place. However, elsewhere in the document, there is a worksheet that is a quick reference to see when a CIP was last conducted. This is shown by the end time of the CIP. So, for example, the last CIP was completed on 6/20 at 05:00. To ensure this reads correctly, we enter in the date the CIP ended in the log when conducting the CIP. Obviously, this would only affect CIPs that go over 00:00. With the way the formulas are, it is showing everything after midnight, as it should. Instead of additionally showing the results from the "3rd shift day prior", it is showing CIPs from the "3rd shift day prior" from the 3rd shift before. 24 hours back if that makes sense. Can you suggest a revision that would look at the end date instead of the start date? The cells it is referencing is correct. We just need to adjust the "logic" to accommodate. Let me know if that doesn't make sense..

In the attached picture, the highlighted cells are the CIPs conducted 24 hours prior and should be replaced with the CIPs conducted "3rd shift day prior".

Appreciate your time,

 

[KRice]

I'm not entirely clear on your point, but I think this might be why... In your first post, column Y is labeled "CIP Date", which is being populated by the FILTER function pulling information from columns Form!A:BH. I took some liberties and assumed that the "CIP Date" shown maps back to a Form column containing CIP Start Dates, but perhaps this isn't correct? Are you saying those are actually CIP End Dates?

Then for the two red-boxed lines in your last post, are you saying the first one began on 6/18 at 23:30 and ended on 6/19 at 04:23? And the second one began on 6/18 at 23:42 and ended on 6/19 at 01:02?

If a job begins during one shift and ends during another shift, which shift report should it appear in...the starting shift or the ending shift?

 

[Austin Lang]

Hopefully this helps clear things up. The other worksheet that I was referencing has tables that look like this.. It shows the most recent CIP that took place as a quick reference. All these are is a combo of the start date and the end time together. Logically, it makes sense to use the start date as the CIP date. However, if a CIP starts on 6/19 and is completed on 06/20, it showing as 6/19 on this sheet would be incorrect and it would show 1 day immediately after CIP concluded. So, to fix the issue, they use the end date. It may be easier to say that the date entered is always the end date, and not the start date.

To answer the first question, yes. Those would have been started on 6/18. For the 2nd question though, we would want it to show CIPs when they are completed because it utilizes the end time when filtering.