How to ignore blank cells within formula?

Options
lauratmc
lauratmc ✭✭
edited 12/09/19 in Formulas and Functions

Hello,

i have a formula which checks compliance between two dates (if one is greater than the other then it returns ‘no’, otherwise ‘yes’). Please could someone help me to update the formula to leave the result blank if the value in one of the other cells is blank? Currently it’s saying ‘yes’ when there’s no finalised date.

 

Thanks!

Laura

B6CA13E6-D8AE-4A05-8BD8-652BF7676178.jpeg

Tags:

Comments

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    Options

    Can you share your formula you are using? You need to add the ISBLANK function to it to get the result you are looking for. 

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    Options

    Add this to the front of your formula. You'll need to fix the column names in mine to match your formula. Add your current working formula to the end where I put "input" and remove those quotes. That should work for you.

    Basically, it'll check your two columns first to see if either are blank, if true, it'll leave it blank, if not, it'll move on to your formula. 

    =IF(OR(ISBLANK([Date 1]@row), ISBLANK([Date 2]@row)), "", "Input rest of formula")

  • jcismesia76271
    Options

    Hi Nic

    This is my current formula.  And if either Target Complete Date or Actual Complete Date or blank then I want the cell to be blank

    =IF([Project?]@row = "yes", IF([Actual Complete Date]@row > [Target Complete Date]@row, "NO", "YES"), "")

    I did try to add your formula in front but got an unparsed error.



    Thanks for your help

     

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    Options

    It should be something like this:

    =IF(OR(ISBLANK([Actual Complete Date]@row), ISBLANK([Target Complete Date]@row)), "", IF([Project?]@row = "yes", IF([Actual Complete Date]@row > [Target Complete Date]@row, "NO", "YES")

  • jcismesia76271
    Options

    Nic - that worked!!!  Thanks.  I realize I left out the second "IF" the one in front of my original formula

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    Options

    Are you missing an "AND" function in your IF formula to look at both the Project? and then the data calculation? 

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    Options

    If you need the "AND" function added in, here you go:

    =IF(OR(ISBLANK([Actual Complete Date]@row), ISBLANK([Target Complete Date]@row)), "", IF(AND([Project?]@row = "Yes", [Actual Complete Date]@row > [Target Complete Date]@row), "No", "Yes"))

  • jcismesia76271
    Options

    I am not sure - first I want to see if Project is Yes then I want to do the Yes/No calculation based on if Actual Date is later than Target Date.  But if either Actual or Target Date columns are blank then I don't want anything.  It appears to be working well now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!