Forum Discussion
if statement for multiple date conditions
The problem was that Excel expects a space before AM/PM. I have converted the values for you - see attached.
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.
HansVogelaar the dates aren't text values, they're in date format. I've attached the file for reference.
They ARE text values, not dates, even though they look like dates and the number format is Date. You can see this because the values are left-aligned while horizontal alignment is set to General. Real dates are right-aligned. And the formula =ISTEXT([@[Target Delivery (Late)]]) returns TRUE:
Your formulas won't work correctly until you have converted the text values to real date/time values. You might try the following:
Enter 1 in an empty cell.
Copy it.
Select the target delivery values.
Right-click in the selection and select Paste Special...
Select Multiply and click OK.
If necessary, apply a date+time format.
Does that make the values right-aligned?
If so, you can do the same for the actual delivery values. Make sure not to include the empty cells.
 
- That makes sense. Apologies for not understanding. I tried the conversion that you gave me and it didn't change the values to right aligned. I've also alternatively tried the text to columns way to format and have had no luck 😕
This seemingly simple task has been the bane of my existence.