lookup functions errors

[rcocrane99]

Hello everyone, I've been stuck working on some data these last few days and I need to find a way to extract 2 pieces of data per "production day". My company currently operates from about 7 am to 1 am and I need to get a precise "Start" and "Shutdown" time (see row 5 and 6) to then get a "run time". The sample set continues and we will just keep adding as we go so I need something that will kind of just update on another sheet over time.

I've tried, nested xlookups, minifs and maxifs and index match but I couldn't really understand it so if anyone has a creative solution have a wack at it.
Would like to have all of the data here eventually:

 

[Cubist]

Error means not found. In cell DJ6. It's 2/4 instead of 2/5. Typo?

 

[rcocrane99]

Error means not found. In cell DJ6. It's 2/4 instead of 2/5. Typo?

Yes!!!!!!!!!!!!!!!!!
I column D I have the times subtracted and in E i have an if statement to ignore zeros and really large numbers, but any idea why in C14 it is retrieving my start date for B15?

***edit*** date was put in by an operator and it wasn't reading the format correctly

 

[Cubist]

The Stop formula assumes that there's always a pairing of Start and Stop. It is looking for the next higher time. This indicates that you don't have a shutdown time for 24-Apr-2024.

 

[rcocrane99]

The Stop formula assumes that there's always a pairing of Start and Stop. It is looking for the next higher time. This indicates that you don't have a shutdown time for 24-Apr-2024.

One last thing, i believe im 99% there, on May 10 2024 we did shut down but at 2:45 pm (sometimes we shutdown before midnight) is there any way to include that in the equation, I see you have a condition that the date is equal to the next date (i.e may 11). I really appreciate all your help with this!!!

 

[Cubist]

The Stop formula I provided does not check for the date. It looks like the next timestamp that is sequentially right after the Start time, so I don't understand your question.

 

[rcocrane99]

The Stop formula I provided does not check for the date. It looks like the next timestamp that is sequentially right after the Start time, so I don't understand your question.

Sorry so for example on May 10 we ran from 7:30 am and shut down early at 2:45 pm but it seems the equation cannot find that time and report it as the stop time.

 

[Cubist]

The issue might be because your data is registering as 2:45 AM, before 7:30 AM.

 

[rcocrane99]

no sorry

 

[Cubist]

I can't replicate the error. Use XL2BB to upload your sheet.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1		Start	Shutdown	Runtime		Start	Shutdown	Start	Shutdown	Start	Shutdown
2	5/9/24	5/9/24 7:35	5/9/24 12:15			5/9/24 7:35	5/9/24 12:15	5/10/24 7:35	5/10/24 14:45	5/11/24 15:00	5/11/24 23:00
3	5/10/24	5/10/24 7:35	5/10/24 14:45
4	5/11/24	5/11/24 15:00	5/11/24 23:00
Sheet9

Cell Formulas
Range	Formula
B2:B4	B2	=XLOOKUP(1,(INT(A2)=INT($F$2:$J$2))*($F$1:$K$1=B$1),$F$2:$K$2)
C2:C4	C2	=MINIFS($F$2:$K$2,$F$2:$K$2,">"&B2)

 

[rcocrane99]

Sorry I cannot use xl2bb as my work computer permissions have restricted it.
I went back to the original formula you have about and it seems to work out, I added those extra parts to eliminate the empy cells from showing 15/apr/24 00:15

=IF(B6=0,0,IF(INT(MINIFS('GaSX Runsheets'!$6:$6,'GaSX Runsheets'!$6:$6,">"&B6))=A7,MINIFS('GaSX Runsheets'!$6:$6,'GaSX Runsheets'!$6:$6,">"&B6),0))