Issue with 00-Jan-00 formula return

[Koimaster]

Dear experts,

As I am not seeing the solution, I wanted to ask you experts if you could help me out.
This is my current formula working with dates:

=IF(W927>V927;"DELAYED DELIVERED";IF(ISBLANK(W927);IF(TODAY()>V927; "NOT YET DELIVERED (LATE)";"NOT YET DELIVERED");"DELIVERED WITHIN LEADTIME"))

V= Expected delivery date
W = Delivery date (returned from another sheet, so it might return a result that is blank as it might not have been delivered yet, but then returns 00-Jan-00 or if no data #N/A.
It works like a charm for most.
However when cell "W" returns from another sheet, that has a blank date field, the value 00-Jan-00 it provides the result "DELIVERED WITHIN LEADTIME".
However i would expect the return "NOT YET DELIVERED" or "NOT YET DELIVERED (LATE)" depending on today's date being later or earlier than cell "V".
With the return of #N/A it perfectly provides "NOT YET DELIVERED"
It's just not working with the 00-Jan-00

Ad I tried so many things, but I just do not see it unfortunately.

Any advice here that I could maybe use?

Appreciated your support already!

Thank you!

 

[Alex Blakenburg]

Please show us the formula in W ?
This will most likely give you what you need but it would be better to clean up you incoming data.

=IF(W927>V927;"DELAYED DELIVERED";IF(OR(ISBLANK(W927);W927=0);IF(TODAY()>V927; "NOT YET DELIVERED (LATE)";"NOT YET DELIVERED");"DELIVERED WITHIN LEADTIME"))

 

[Koimaster]

Hi Alex,

Thank you for your prompt reply.
W would be: =VLOOKUP(A913;'SHIPMENT REPORT'!A:G;7;FALSE)

Wauw, this formula already indeed shows what it should show.

A BIG thank you!

 

[Alex Blakenburg]

Typically if the date is blank or #N/A you would handle it in the lookup formula eg:

=LET(returnValue;IFERROR(VLOOKUP(A927;'SHIPMENT REPORT123451'!A:G;7;FALSE);"");
            IF(returnValue="";"";returnValue))

or

=LET(returnValue;XLOOKUP(A927;'SHIPMENT REPORT123451'!A:A;'SHIPMENT REPORT123451'!G:G;"");
            IF(returnValue="";"";returnValue))

This would change your other formula to this:

=IF(W927>V927;"DELAYED DELIVERED";IF(W927="";IF(TODAY()>V927; "NOT YET DELIVERED (LATE)";"NOT YET DELIVERED");"DELIVERED WITHIN LEADTIME"))