How to ignore blank cells within formula?

lauratmc

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

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