Forum Discussion
if statement for multiple date conditions
I'm looking to create an if statement that returns the 4 scenarios listed below. The highlighted scenarios in red are where I seem to be having difficulties.
Confirmed Late = Actual Delivery is after Target Delivery
Unconfirmed Late = Actual Delivery is blank and the current date is after Target Delivery
On Time = Actual Deliver is before Target Delivery
In Transit = Actual Delivery is blank and the current date is before Target Delivery
My current formula:
=IF([@[Actual Delivery]]>=[@[Target Delivery (Late)]], "Confirmed Late", IF([@[Actual Delivery]]<[@[Target Delivery (Late)]],"On Time", ""))
Data sample attached - any help would be greatly appreciated!!
The problem was that Excel expects a space before AM/PM. I have converted the values for you - see attached.
9 Replies
Perhaps
=IF([@[Actual Delivery]]="", IF(NOW()<[@[Target Delivery (Late)]],"In Transit","Uncomfird late"), IF([@[Actual Delivery]]<[@[Target Delivery (Late)]],"On Time","Confirmed late") )
I went ahead and tried this formula but it seems to be missing the "Unconfirmed Late" flag. he highlighted fields in the screenshot are returning as In Transit but should flag as Unconfirmed late because it is now after the Target Delivery Date.
The main problem is that columns A and B contain text values that look like dates, not real dates. A tell-tale sign is that they are left-aligned.
Once you have corrected that, you can use the formula
=IF([@[Actual Delivery]]="",IF([@[Target Delivery (Late)]]>NOW(),"In Transit","Unconfirmed Late"),IF([@[Actual Delivery]]<=[@[Target Delivery (Late)]],"On Time","Confirmed Late"))
Hi thanks for the insight, I've corrected the dates! The formula provided seems to get me most of the way there but appears to miss the "unconfirmed late" piece.
The highlighted fields in the screenshot are returning as In Transit but should flag as Unconfirmed late because it is now after the Target Delivery Date.
The dates are still left-aligned, so I suspect they are still text values instead of dates.
