Having trouble with multiple IF statement

%3CLINGO-SUB%20id%3D%22lingo-sub-1008228%22%20slang%3D%22en-US%22%3EHaving%20trouble%20with%20multiple%20IF%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1008228%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EOk%2C%20so%20I%20have%20three%20columns%3A%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EPO%20Required%20By%20(calculated%20date%20column)%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20Date%20Needed%20to%20Ship%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Leadtime%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20first%20IF%20statement%20works%20fine%20if%20both%20Date%20Needed%20to%20Ship%20%26nbsp%3B%20and%20%26nbsp%3B%26nbsp%3B%20Leadtime%26nbsp%3B%20have%20the%20NA%20value%20selected.%20%26nbsp%3B%26nbsp%3B%20The%20result%20shows%20NA%20in%20the%20PO%20Required%20By%20column.%20%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20problem%20is%20that%20I%20need%20another%20IF%20statement%20(or%20maybe%20two%20more)%20that%20allows%20for%20Leadtime%20or%20Date%20Needed%20to%20Ship%20to%20have%20a%20value%20other%20than%20NA%2C%20but%20still%20allow%20PO%20Required%20By%20to%20say%20NA.%20%26nbsp%3B%26nbsp%3B%20The%20reason%20for%20this%20is%20that%20our%20shipping%20team%20may%20know%20the%20Leadtime%20for%20production%2C%20but%20still%20not%20yet%20know%20when%20our%20client%20wants%20something%20to%20ship.%20%26nbsp%3B%20So%20they%20may%20put%20the%20Leadtime%20value%20in%2C%20but%20the%20Date%20Needed%20to%20Ship%20will%20still%20be%20blank%20(or%20vice%20versa).%20%26nbsp%3B%20In%20this%20case%2C%20the%20PO%20Required%20By%20should%20needs%20to%20still%20show%20NA.%20%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EONLY%20when%20both%20the%20Date%20Needed%20to%20Ship%2C%20and%20the%20Leadtime%20have%20values%2C%20should%20the%20calculated%20column%20perform%20the%20math%3A%20%26nbsp%3B%26nbsp%3B%20Date%20Needed%20to%20Ship%20minus%20Leadtime.%20%26nbsp%3B%20%26nbsp%3B%20This%20gives%20a%20date%20value%20in%20the%20PO%20Required%20by%20Column.%20%26nbsp%3B%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EHere%20is%20what%20I%20have%20so%20far%20but%20I%20always%20get%20a%20syntax%20error.%20%26nbsp%3B%20Please%20help!%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DIF(AND(%5BDate%20Needed%20to%20Ship%5D%3D%22NA%22%2COR(Leadtime%3D%22NA%22%2C))%2C%22NA%22%2C%22NA%22)%2CIF(AND(%5BDate%20Needed%20to%20Ship%5D%26lt%3B%26gt%3B%22NA%22%2CAND(Leadtime%26lt%3B%26gt%3B%22NA%22%2C))%2C%5BDate%20Needed%20to%20Ship%5D-Leadtime)%2C%22NA%22))%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1008228%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional Visitor

Ok, so I have three columns: 

 

PO Required By (calculated date column)      Date Needed to Ship         Leadtime

 

The first IF statement works fine if both Date Needed to Ship   and    Leadtime  have the NA value selected.    The result shows NA in the PO Required By column.  

 

The problem is that I need another IF statement (or maybe two more) that allows for Leadtime or Date Needed to Ship to have a value other than NA, but still allow PO Required By to say NA.    The reason for this is that our shipping team may know the Leadtime for production, but still not yet know when our client wants something to ship.   So they may put the Leadtime value in, but the Date Needed to Ship will still be blank (or vice versa).   In this case, the PO Required By should needs to still show NA.  

 

ONLY when both the Date Needed to Ship, and the Leadtime have values, should the calculated column perform the math:    Date Needed to Ship minus Leadtime.     This gives a date value in the PO Required by Column.   

 

Here is what I have so far but I always get a syntax error.   Please help!

 

=IF(AND([Date Needed to Ship]="NA",OR(Leadtime="NA",)),"NA","NA"),IF(AND([Date Needed to Ship]<>"NA",AND(Leadtime<>"NA",)),[Date Needed to Ship]-Leadtime),"NA"))

0 Replies