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:

 

[myall_blues]

Here's one way. Since you're using 365 there's no doubt a more compact formula available.

Book1
	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE	DF	DG	DH	DI	DJ
5		Start	Shutdown	Runtime		Start									Shutdown	Start
6	1/05/2024	1/05/2024 7:30				1/05/2024 7:30									2/05/2024 0:15	2/05/2024 8:40
7	2/05/2024	2/05/2024 8:40	2/05/2024 0:15	16:45
8	3/05/2024
9	4/05/2024
10	5/05/2024
11	6/05/2024
12	7/05/2024
13	8/05/2024
14	9/05/2024
15	10/05/2024
16	11/05/2024
Sheet1

Cell Formulas
Range	Formula
CV6:CW16	CV6	=IF(SUMPRODUCT(($CZ$5:$DL$5=CV$5)*(DATE(YEAR($CZ$6:$DL$6),MONTH($CZ$6:$DL$6),DAY($CZ$6:$DL$6))=$CU6)*$CZ$6:$DL$6)=0,"",SUMPRODUCT(($CZ$5:$DL$5=CV$5)*(DATE(YEAR($CZ$6:$DL$6),MONTH($CZ$6:$DL$6),DAY($CZ$6:$DL$6))=$CU6)*$CZ$6:$DL$6))
CX7	CX7	=CW7-CV6

 

[rcocrane99]

Here's one way. Since you're using 365 there's no doubt a more compact formula available.

Book1
	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE	DF	DG	DH	DI	DJ
5		Start	Shutdown	Runtime		Start									Shutdown	Start
6	1/05/2024	1/05/2024 7:30				1/05/2024 7:30									2/05/2024 0:15	2/05/2024 8:40
7	2/05/2024	2/05/2024 8:40	2/05/2024 0:15	16:45
8	3/05/2024
9	4/05/2024
10	5/05/2024
11	6/05/2024
12	7/05/2024
13	8/05/2024
14	9/05/2024
15	10/05/2024
16	11/05/2024
Sheet1

Cell Formulas
Range	Formula
CV6:CW16	CV6	=IF(SUMPRODUCT(($CZ$5:$DL$5=CV$5)*(DATE(YEAR($CZ$6:$DL$6),MONTH($CZ$6:$DL$6),DAY($CZ$6:$DL$6))=$CU6)*$CZ$6:$DL$6)=0,"",SUMPRODUCT(($CZ$5:$DL$5=CV$5)*(DATE(YEAR($CZ$6:$DL$6),MONTH($CZ$6:$DL$6),DAY($CZ$6:$DL$6))=$CU6)*$CZ$6:$DL$6))
CX7	CX7	=CW7-CV6

close! the reason I think I am struggling so much is the time goes past midnight therefore changing the day, so for example inCW7 the result you have should actually be in CW6

 

[Cubist]

Perhaps.

Book1
	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE	DF	DG	DH	DI	DJ
5		Start	Shutdown	Runtime		Start									Shutdown	Start
6	5/1/24	5/1/24 7:30	5/2/24 0:15	16:45:00		5/1/24 7:30									5/2/24 0:15	5/2/24 8:40
7	5/2/24	5/2/24 8:40
8	5/3/24
9	5/4/24
10	5/5/24
11	5/6/24
12	5/7/24
13	5/8/24
14	5/9/24
15	5/10/24
16	5/11/24
Sheet1

Cell Formulas
Range	Formula
CW6	CW6	=MINIFS(CZ6:DJ6,CZ6:DJ6,">"&CV6)
CX6	CX6	=CW6-CV6
CV6:CV7	CV6	=XLOOKUP(1,(INT($CU6)=INT($CZ$6:$DJ$6))*($CZ$5:$DJ$5=CV$5),$CZ$6:$DJ$6)

 

[rcocrane99]

This looks right given the sheet you have provided, I'll take a look tomorrow morning when I get to work! Just to clarify what is the CZ6 cell for and how does the CZ6:DJ6 and CZ5:DJ6 affect the equation? Sorry still an Excel newbie

 

[rcocrane99]

just seeing the scroll bar now lol, do you know if these work across different sheets or does all of the data need to be on one?

 

[Cubist]

It should work across sheets. It might be easier to get the formula right on the same sheet and cut that cell into your desired sheet.

 

[rcocrane99]

View attachment 111391
see DA36 cell for referenced formula any ideas?

 

[Cubist]

Are the date cells date type and not string?
You can check by =ISNUMBER(). If true then it's a date, else it's a string. If it's string you need to convert it to date type.

 

[rcocrane99]

looks like its almost there, cell formulas to the right not sure why it doesn't fill down with the other cells