Not Counting Blank cells in Checkbox formula

renee.duke99201

Hi All,

desperately need your help on this one. I've been searching the forums and tried this formula so many different ways and cannot get it to work.   

I have checkbox formula that is simple enough, I want it to check the box if we met the delivery date that was planned. So I'm comparing delivery date planned vs. Delivery date actual. This formula is working fine. IF([Delivery Date Actual]1 <= [Delivery Date Plan]1), 1, 0).

The problem is I do not want it to check the box if either of the cells is empty in either of these columns. I've tried using NOT(ISBLANK and the <>, "" formulas and it either changes all boxes to unchecked or doesn't work at all.



Any help is much appreciated!! Apologies if I'm overlooking something simple!

Andrée Starå

Hi Renee,

Try something like.

=IF(OR(ISBLANK([Date 1]@row), ISBLANK([Date 2]@row)), 0, IF([Delivery Date Actual]1 <= [Delivery Date Plan]1), 1, 0)

Did it work?

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Mike Wilday

Try this one. It checks to see if either of the dates are blank and leaves the cell blank... 

IF(OR(ISBLANK([Deliver Date Actual]@row),ISBLANK([Delivery Date Plan]@row)),"", IF([Delivery Date Actual]@row <= [Delivery Date Plan]@row), 1, 0).

Mike Wilday

Or use Andree's suggestion who was 1 minute faster than mine!  

renee.duke99201

Thank you guys so much for the quick reply!

Unfortunately they both came back Unparseable  

What am I doing wrong? 

Andrée Starå

Happy to help!

Can you maybe share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

Andrée Starå

@Mike

Haha!

Mike Wilday

Make sure your actual column header names are in there. If there are spaces or they end in a number then you'll need to surround the column names with Brackets. [Column Name]@row. 

Mike Wilday

renee.duke99201

Yes sir here is the formula I used

=IF(OR(ISBLANK([Deliver Date Actual]@row),ISBLANK([Delivery Date Plan]@row)),"", IF([Delivery Date Actual]@row <= [Delivery Date Plan]@row), 1, 0)

renee.duke99201

Here's a screenshot if it helps. 

The Unparseable cells are where I pasted the old formula. The cells that have checkboxes are the old formula but as you can see it's checking boxes for blank cells also. 

Thanks again for your help!! 

Mike Wilday

Can you add a screenshot of the formula you added in one of those unparseable rows? 

Mike Wilday

I see... I failed to close the statements correctly... 

=IF(OR(ISBLANK([Deliver Date Actual]@row),ISBLANK([Delivery Date Plan]@row)),0, IF([Delivery Date Actual]@row <= [Delivery Date Plan]@row), 1, 0))

That should do the trick. It was missing a closing parenthesis. Did that work? I also adjusted my "" to be a zero which is in essence the same thing. 

renee.duke99201

I copied and pasted directly from your post and it's still showing unparseable.  

I even refreshed the report just to make sure everything calculated. I don't know why it's not working 

 

I pasted: =IF(OR(ISBLANK([Deliver Date Actual]@row),ISBLANK([Delivery Date Plan]@row)),0, IF([Delivery Date Actual]@row <= [Delivery Date Plan]@row), 1, 0))

Paul Newcome

There is an extra closing parenthesis in the logical statement portion of the second IF.

 

What is there...

........................IF([Delivery Date Actual]@row <= [Delivery Date Plan]@row), 1, .........

What it should be...

........................IF([Delivery Date Actual]@row <= [Delivery Date Plan]@row, 1, .........

 

renee.duke99201

Like this

=IF(OR(ISBLANK([Delivery Date Actual]2@row),ISBLANK([Delivery Date Plan]2@row)),0, IF([Delivery Date Actual]2@row <= [Delivery Date Plan]2@row, 1, 0))

 

Still Unpareasble.