IF Statement Issue

[mrsushi]

For some reason when the time in Column C (09:15) is greater than Column D (08:15), doesn't return "LATE"

Any ideas pls?

=IF(C4>D4, IF(OR(H4="BBA", H4="DRFI", H4="EUR", H4="7 Day EUR LIBID", H4="LGI6"), "Late Delivery", IF(OR(H4="NYFR", H4="NYFR"), "Delayed Delivery PM", IF(OR(H4="SONIA", H4="PFBL98"), "Delayed Delivery AM"))),  IF(C4<D4, "Delivered", "Late"))

 

[awoohaw]

I just realized you don't have XMATCH. Try this instead:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	File Name	DATE/TIME	47173	Deadline	Status		Path	File
2		01/03/2024 05:31	0.385416667	0.34375	Late Delivery			BBAR			BBAR	Late Delivery
3		01/03/2024 05:31	0.416666667	0.34375	Late Delivery			E - 7 Day EUR LIBID			E - 7 Day EUR LIBID	Late Delivery
4		01/03/2024 05:31	0.458333333	0.34375	Delayed Delivery PM			NYFRCUST			NYFRCUST	Delayed Delivery PM
5		01/03/2024 04:15	0.489583333	0.34375	Delayed Delivery AM			PFBL98			PFBL98	Delayed Delivery AM
6		01/03/2024 04:45	0.375	0.34375				CLOSE
7		01/03/2024 05:34	0.375	0.34375				OPEN
8		02/03/2024 05:34	0.375	0.34375				OPEN
9
10
11			0.385416667	0.34375	Late Delivery	Late Delivery		BBAR
12			0.416666667	0.34375	Late Delivery	Late Delivery		E - 7 Day EUR LIBID
13			0.458333333	0.34375	Delayed Delivery PM	Delayed Delivery PM		NYFRCUST
14			0.489583333	0.34375	Delayed Delivery AM	Delayed Delivery AM		PFBL98
15			0.375	0.34375		Late		CLOSE
16			0.375	0.34375		Late		OPEN
17			0.375	0.34375		Late		OPEN
Sheet1

Cell Formulas
Range	Formula
F11:F17	F11	=IF(C11>D11,IFERROR(INDEX($L$2:$L$5,MATCH(H11,$K$2:$K$5,0)),"Late"),"Delivered")

 

[mrsushi]

try this:

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	File Name	DATE/TIME	2029-02-24	Deadline	Status		Path	File
2		01/03/2024 05:31	09:15	08:15	Late Delivery			BBAR			BBAR	Late Delivery
3		01/03/2024 05:31	10:00	08:15	Late Delivery			E - 7 Day EUR LIBID			E - 7 Day EUR LIBID	Late Delivery
4		01/03/2024 05:31	11:00	08:15	Delayed Delivery PM			NYFRCUST			NYFRCUST	Delayed Delivery PM
5		01/03/2024 04:15	11:45	08:15	Delayed Delivery AM			PFBL98			PFBL98	Delayed Delivery AM
6		01/03/2024 04:45	09:00	08:15				CLOSE
7		01/03/2024 05:34	09:00	08:15				OPEN
8		02/03/2024 05:34	09:00	08:15				OPEN
9
10
11			09:15	08:15	Late Delivery	Late Delivery		BBAR
12			10:00	08:15	Late Delivery	Late Delivery		E - 7 Day EUR LIBID
13			11:00	08:15	Delayed Delivery PM	Delayed Delivery PM		NYFRCUST
14			11:45	08:15	Delayed Delivery AM	Delayed Delivery AM		PFBL98
15			09:00	08:15		Late		CLOSE
16			09:00	08:15		Late		OPEN
17			09:00	08:15		Late		OPEN
Sheet1

Cell Formulas
Range	Formula
F11:F17	F11	=IF(C11>D11,XLOOKUP(H11,K2:K5,L2:L5,"Late",0,1),"Delivered")

Thats

I just realized you don't have XMATCH. Try this instead:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	File Name	DATE/TIME	47173	Deadline	Status		Path	File
2		01/03/2024 05:31	0.385416667	0.34375	Late Delivery			BBAR			BBAR	Late Delivery
3		01/03/2024 05:31	0.416666667	0.34375	Late Delivery			E - 7 Day EUR LIBID			E - 7 Day EUR LIBID	Late Delivery
4		01/03/2024 05:31	0.458333333	0.34375	Delayed Delivery PM			NYFRCUST			NYFRCUST	Delayed Delivery PM
5		01/03/2024 04:15	0.489583333	0.34375	Delayed Delivery AM			PFBL98			PFBL98	Delayed Delivery AM
6		01/03/2024 04:45	0.375	0.34375				CLOSE
7		01/03/2024 05:34	0.375	0.34375				OPEN
8		02/03/2024 05:34	0.375	0.34375				OPEN
9
10
11			0.385416667	0.34375	Late Delivery	Late Delivery		BBAR
12			0.416666667	0.34375	Late Delivery	Late Delivery		E - 7 Day EUR LIBID
13			0.458333333	0.34375	Delayed Delivery PM	Delayed Delivery PM		NYFRCUST
14			0.489583333	0.34375	Delayed Delivery AM	Delayed Delivery AM		PFBL98
15			0.375	0.34375		Late		CLOSE
16			0.375	0.34375		Late		OPEN
17			0.375	0.34375		Late		OPEN
Sheet1

Cell Formulas
Range	Formula
F11:F17	F11	=IF(C11>D11,IFERROR(INDEX($L$2:$L$5,MATCH(H11,$K$2:$K$5,0)),"Late"),"Delivered")

Many thanks for the statement. That's worked.

Much appreciated.

Regards

 

[awoohaw]

Thats


Many thanks for the statement. That's worked.

Much appreciated.

Regards

Happy to help.
Best wishes.