locked
Using IF AND OR in calculated (date) column RRS feed

  • Question

  • I have three columns in a SharePoint list: 

    PO Required By     Date Needed to Ship     Leadtime

    The goal is to have the user input the Date Needed to Ship, then at the same time, or perhaps later, enter the Leadtime.  

    The PO Required By will be a calculated date  (Date Needed to Ship - Leadtime).     The idea being that this will tell our sales staff when they need to get a purchase order in the system.  

    I want all of the fields to be blank (or NA is fine) when no data is there.  

    I also want the PO Required By to remain blank if only one of the other two fields has an entry.   Currently this formula works:

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

    This works if I have Leadtime and Date Need to Ship By  filled in  (it calculates a correct date for the PO Required By field).  

    However, if one of the fields has a value, and the other does not, it ends up showing a strange value, like "256".   

    What change do I need to make to make the PO Required By to still show a blank value if only one of the other two fields has a value.  

    In our system, someone may know the date they want something to ship, but they may not know the lead time until checking with production (or vice versa).   I just don't want a weird value showing up if only one of the two fields is filled in. 

    Thanks!

    Wednesday, November 13, 2019 10:03 PM