SOLVED

if statement for multiple date conditions

%3CLINGO-SUB%20id%3D%22lingo-sub-2308469%22%20slang%3D%22en-US%22%3Eif%20statement%20for%20multiple%20date%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2308469%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20looking%20to%20create%20an%20if%20statement%20that%20returns%20the%204%20scenarios%20listed%20below.%20The%20highlighted%20scenarios%20in%20red%20are%20where%20I%20seem%20to%20be%20having%20difficulties.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EConfirmed%20Late%20%3D%20Actual%20Delivery%20is%20%3CSTRONG%3Eafter%3C%2FSTRONG%3E%20Target%20Delivery%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3EUnconfirmed%20Late%20%3D%20Actual%20Delivery%20is%20blank%20but%20the%20current%20date%20is%20%3CSTRONG%3Eafter%3C%2FSTRONG%3E%20Target%20Delivery%3C%2FFONT%3E%3C%2FP%3E%3CP%3EOn%20Time%20%3D%20Actual%20Deliver%20is%20before%20Target%20Delivery%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3EIn%20Transit%20%3D%20Actual%20Delivery%20is%20blank%20but%20the%20current%20date%20is%20%3CSTRONG%3Ebefore%3C%2FSTRONG%3E%20Target%20Delivery%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%3EMy%20current%20formula%3A%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%3E%3DIF(%5B%40%5BActual%20Delivery%5D%5D%26gt%3B%3D%5B%40%5BTarget%20Delivery%20(Late)%5D%5D%2C%20%22Confirmed%20Late%22%2C%20IF(%5B%40%5BActual%20Delivery%5D%5D%26lt%3B%5B%40%5BTarget%20Delivery%20(Late)%5D%5D%2C%22On%20Time%22%2C%20%22%22))%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EData%20sample%20attached%20-%20any%20help%20would%20be%20greatly%20appreciated!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2308469%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2308574%22%20slang%3D%22en-US%22%3ERe%3A%20if%20statement%20for%20multiple%20date%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2308574%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1041172%22%20target%3D%22_blank%22%3E%40ncstle%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20main%20problem%20is%20that%20columns%20A%20and%20B%20contain%20text%20values%20that%20look%20like%20dates%2C%20not%20real%20dates.%20A%20tell-tale%20sign%20is%20that%20they%20are%20left-aligned.%3C%2FP%3E%0A%3CP%3EOnce%20you%20have%20corrected%20that%2C%20you%20can%20use%20the%20formula%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(%5B%40%5BActual%20Delivery%5D%5D%3D%22%22%2CIF(%5B%40%5BTarget%20Delivery%20(Late)%5D%5D%26gt%3BNOW()%2C%22In%20Transit%22%2C%22Unconfirmed%20Late%22)%2CIF(%5B%40%5BActual%20Delivery%5D%5D%26lt%3B%3D%5B%40%5BTarget%20Delivery%20(Late)%5D%5D%2C%22On%20Time%22%2C%22Confirmed%20Late%22))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2308578%22%20slang%3D%22en-US%22%3ERe%3A%20if%20statement%20for%20multiple%20date%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2308578%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1041172%22%20target%3D%22_blank%22%3E%40ncstle%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(%5B%40%5BActual%20Delivery%5D%5D%3D%22%22%2C%0A%20%20%20%20%20IF(NOW()%26lt%3B%5B%40%5BTarget%20Delivery%20(Late)%5D%5D%2C%22In%20Transit%22%2C%22Uncomfird%20late%22)%2C%0A%20%20%20%20%20IF(%5B%40%5BActual%20Delivery%5D%5D%26lt%3B%5B%40%5BTarget%20Delivery%20(Late)%5D%5D%2C%22On%20Time%22%2C%22Confirmed%20late%22)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2308593%22%20slang%3D%22en-US%22%3ERe%3A%20if%20statement%20for%20multiple%20date%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2308593%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20thanks%20for%20the%20insight%2C%20I've%20corrected%20the%20dates!%20The%20formula%20provided%20seems%20to%20get%20me%20most%20of%20the%20way%20there%20but%20appears%20to%20miss%20the%20%22unconfirmed%20late%22%20piece.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20highlighted%20fields%20in%20the%20screenshot%20are%20returning%20as%20In%20Transit%20but%20should%20flag%20as%20Unconfirmed%20late%20because%20it%20is%20now%20after%20the%20Target%20Delivery%20Date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ncstle_0-1619731003131.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F276942i36FA040CE1AE8DB9%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22ncstle_0-1619731003131.png%22%20alt%3D%22ncstle_0-1619731003131.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2308604%22%20slang%3D%22en-US%22%3ERe%3A%20if%20statement%20for%20multiple%20date%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2308604%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI%20went%20ahead%20and%20tried%20this%20formula%20but%20it%20seems%20to%20be%20missing%20the%20%22Unconfirmed%20Late%22%20flag.%26nbsp%3B%3CSPAN%3Ehe%20highlighted%20fields%20in%20the%20screenshot%20are%20returning%20as%20In%20Transit%20but%20should%20flag%20as%20Unconfirmed%20late%20because%20it%20is%20now%20after%20the%20Target%20Delivery%20Date.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ncstle_0-1619731121644.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F276943iC01F8FF56A08BE73%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22ncstle_0-1619731121644.png%22%20alt%3D%22ncstle_0-1619731121644.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2308800%22%20slang%3D%22en-US%22%3ERe%3A%20if%20statement%20for%20multiple%20date%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2308800%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1041172%22%20target%3D%22_blank%22%3E%40ncstle%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20dates%20are%20still%20left-aligned%2C%20so%20I%20suspect%20they%20are%20still%20text%20values%20instead%20of%20dates.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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!!

 

9 Replies

@ncstle 

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

@ncstle 

Perhaps

=IF([@[Actual Delivery]]="",
     IF(NOW()<[@[Target Delivery (Late)]],"In Transit","Uncomfird late"),
     IF([@[Actual Delivery]]<[@[Target Delivery (Late)]],"On Time","Confirmed late")
)

@Hans Vogelaar 

 

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.

 

ncstle_0-1619731003131.png

 

@Sergei Baklan 


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.

 

ncstle_0-1619731121644.png

 

@ncstle 

The dates are still left-aligned, so I suspect they are still text values instead of dates.

@Hans Vogelaar the dates aren't text values, they're in date format. I've attached the file for reference. 

@ncstle 

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:

 

S0354.png

 

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.
best response confirmed by ncstle (Occasional Contributor)
Solution

@ncstle 

The problem was that Excel expects a space before AM/PM. I have converted the values for you - see attached.