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]

Please post an xl2bb of your worksheet? (link for xl2bb add in is below).

Please verify your time formats and values. what is the value of cells C4 and D4? You may have one on 24 hr day, and one on AM/PM day? Try formatting both columns as hh:mm:ss and see if you see any differences.

 

[mrsushi]

Please post an xl2bb of your worksheet? (link for xl2bb add in is below).

Please verify your time formats and values. what is the value of cells C4 and D4? You may have one on 24 hr day, and one on AM/PM day? Try formatting both columns as hh:mm:ss and see if you see any differences.

Hi ,

Cant install as I'm working from a work network.

The table is as per below from Column A to H

Formula in Column E is =IF(C2>D2, IF(OR(H2="BBAR", H2="DRFI_GBP", H2="EURI", H2="E - 7 Day EUR LIBID", H2="LGI6_LATE"), "Late Delivery", IF(OR(H2="NYFRCUST", H2="NYFR"), "Delayed Delivery PM", IF(OR(H2="DRFICUST", H2="PFBL98"), "Delayed Delivery AM", ""))), IF(C2<D2, "Delivered", "Late"))

The last 3 cells need to return LATE, but its returning nothing.

File Name	DATE/TIME	29/02/24	Deadline	Status		Path	File
	01/03/2024 05:31	09:15	08:15	Late Delivery			BBAR
	01/03/2024 05:31	10:00	08:15	Late Delivery			E - 7 Day EUR LIBID
	01/03/2024 05:31	11:00	08:15	Delayed Delivery PM			NYFRCUST
	01/03/2024 04:15	11:45	08:15	Delayed Delivery AM			PFBL98
	01/03/2024 04:45	09:00	08:15				CLOSE
	01/03/2024 05:34	09:00	08:15				OPEN
	02/03/2024 05:34	09:00	08:15				OPEN

 

[awoohaw]

When I use your formula I get a blank because that is what it maps to:
=IF(C2>D2,

IF(OR(H2="BBAR", H2="DRFI_GBP", H2="EURI", H2="E - 7 Day EUR LIBID", H2="LGI6_LATE"),​

"Late Delivery",​

IF(OR(H2="NYFRCUST", H2="NYFR"),​

"Delayed Delivery PM",​

IF(OR(H2="DRFICUST", H2="PFBL98"),​

"Delayed Delivery AM",​

""))),​

IF(C2<D2,​

"Delivered",​

"Late"))​

​

​

​

 

[mrsushi]

When I use your formula I get a blank because that is what it maps to:
=IF(C2>D2,

IF(OR(H2="BBAR", H2="DRFI_GBP", H2="EURI", H2="E - 7 Day EUR LIBID", H2="LGI6_LATE"),​

"Late Delivery",​

IF(OR(H2="NYFRCUST", H2="NYFR"),​

"Delayed Delivery PM",​

IF(OR(H2="DRFICUST", H2="PFBL98"),​

"Delayed Delivery AM",​

""))),​

IF(C2<D2,​

"Delivered",​

"Late"))​

​

​

​

oK, how can the formula be amended to return the value of "late" for the last 3 rows? Is there a better way of writing this IF function pls?

 

[awoohaw]

what are the requirements for all statuses?

 

[mrsushi]

what are the requirements for all statuses?

The requirements .....If the times in column C is less than D, then should return "Delivered", otherwise it should return "Late".

However, there in column H, specifies certain files ie BBAR, Open, Close etc, therefore if C is less than D, then return "Delivered", otherwise it should return as follows

BBAR = Late Delivery
E - 7 Day EUR LIBID = Late Delivery
NYFRCUST = Delayed Delivery PM
PFBL98 = Delayed Delivery AM

 

[awoohaw]

This is the problem, you are trying to process the EXACT condition twice, it will never get to the second occurrence.
you need to redefine your logic.

The requirements .....If the times in column C is less than D, then should return "Delivered", otherwise it should return "Late".

However, there in column H, specifies certain files ie BBAR, Open, Close etc, therefore if C is less than D, then return "Delivered", otherwise it should return as follows

 

[mrsushi]

In general, 2 outcomes are needed based on the time in Col C and D ie Delivered or Late. However, is there no way to base this on criteria in Column H?

 

[awoohaw]

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")