How to ignore blank cells within formula?

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

Find more posts tagged with

Formulas

Comments

Nic Larsen

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

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

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

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

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

Nic Larsen

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

Nic Larsen

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

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

Sign in to join the conversation!

Quick Links

Trending discussions

INCORRECT ARGUMENT SET on an =IF function
I'm trying to pull in the contact information of a second HR Business Partner, contingent on the department of the individual, from a separate reference sheet. However, not all departments have a second HRBP— so if the column of "second HRBP contact info" is blank, I don't want anything to be pulled into the new sheet's…
Automation not triggering when % Complete shows '100% instead of 100%
I have an automation set up so that when the Status column changes to Complete, Canceled, or Not Needed, Smartsheet automatically updates the % Complete column to 100% (Text/Number column type). After that, another workflow should trigger to alert the next person that their task is ready. This worked fine before, but…
Index Match - # NO MATCH error
Hello, My index match formula cross sheet is giving error since yesterday. =INDEX({VER DT4DT Tools Range 1}, MATCH([App CI]@row , {VER DT4DT Tools Range 2}, 0)) I have checked the format of columns in both sheet and also retyped formula. any advice?