Using Multiple IFBLANK Checks With IF

StevieSango · 05/19/22

Hi, all;

I'm trying to use an IF statement to compare multiple dates to a deadline date. This is pretty simple. If any one of the dates in question comes before or on the deadline, the condition is Green. If any of them are past the deadline, the condition is Red. The complication comes in when those cells turn up blank, as a blank value defaults to a Green condition.

Using ISBLANK, I've been able to successfully guard against this issue with a single blank cell:

=IF(ISBLANK([Date A]@row), "Red", IF(AND([Date A]@row <= [Deadline]@row, [Date B]@row <= [Deadline]@row), "Green", "Red"))

This gives a Red result if I leave cell [Date A] blank, and otherwise compares dates as required.

Try as I might, though, I cannot figure out how to add more ISBLANK conditions to this formula, such as checking against a blank state for both [Date A] or [Date B]. I've tried using OR in a couple of different ways suggested by other community resources, but that always turns up an UNPARSEABLE result or an INCORRECT ARGUMENT SET. I don't know if I'm using the wrong syntax, or the wrong strategy altogether.

Does anyone know how I might pull this off?

Thanks...

Paul Newcome · 05/19/22

Try this:

=IF(OR([Date A]@row = "", [Date B]@row = "", MAX([Date A]@row, [Date B]@row) > Deadline@row), "Red", "Green")

IF

one is blank

OR

the other is blank

OR

either are past the deadline

then "Red"

otherwise "Green"

StevieSango · 05/20/22

It's a bit clunky, and I had to use two helper columns to make it happen, but I finally figured this out.

First, I used [Date Check] to see if any dates in question are past the Deadline:

=IF(OR([G1 TKO Dwg Level Agreed (NPPC)]@row <= (INDEX({N-36.ref}, MATCH($Project@row, {Project.ref}, 0))), [G1 Basic Plan "Concept" & Assumptions (PE)]@row <= (INDEX({N-36.ref}, MATCH($Project@row, {Project.ref}, 0)))), "Green", "Red")

Second, I used [Blank Check] to check for blank date fields:

=IF(OR([G1 TKO Dwg Level Agreed (NPPC)]@row = "", [G1 Basic Plan "Concept" & Assumptions (PE)]@row = ""), "Red", "Green")

Finally, I used a STATUS column to check the two previous columns with nested IF-OR:

=IF(OR([Date Check]@row = "Red", [Blank Check]@row = "Red"), "Red", "Green")

Hard part's over. I need to duplicate this for a bunch of different deadlines and add a Yellow condition, but those are already well within my wheelhouse. Thanks for your help, both of you, and for the education.

Edit: typo

James Keuning · 05/19/22

both [Date A] or [Date B]

I think you mean both A AND B, not A or B…

Here is how I solve this problem. I create my IF statements, one per column, moving left to right, and make sure that they accurately return the correct result when TRUE. In the FALSE position for each I put 999. No quotes.

so. Now I have my columns, with working formulas, being these in the correct order moving left to right. The, starting from the far right, I copy the working formula and paste it over the 999 in the column to the left. Then I copy that entire formula and paste it over the 999 in the next to the left, and do that all the way to the left. What you are left with is the 999 that results if the formula finds no true result.

StevieSango · 05/19/22

Hi, James! Thanks for the reply.

Unfortunately, I do mean OR. If any date doesn't meet the deadline individually, (and a blank date doesn't meet the deadline) then the Red condition needs to be triggered. If I use [Date A] AND [Date B] then they both have to be blank for a Red condition, and that's not what I want.

I appreciate the tip for combining statements. I've done something similar before (many times before), but never with the placeholder as you describe. That's a cool trick. If I can get an OR statement to work for multiple IFBLANK statements, which I've seen in another post, then I might get it to work with the larger formula this way.

That's provided I'm not using the wrong strategy entirely... but I'll give it a go. I'll let you know what happens.

Thanks again!

Paul Newcome · 05/19/22

Try this:

=IF(OR([Date A]@row = "", [Date B]@row = "", MAX([Date A]@row, [Date B]@row) > Deadline@row), "Red", "Green")

IF

one is blank

OR

the other is blank

OR

either are past the deadline

then "Red"

otherwise "Green"

StevieSango · 05/19/22

Thank you, Paul!

That works. I'd looked into setting Red as the default condition but couldn't figure out how. Very nice. I also learned that "" (can you put quotes inside quotes?) is equivalent to a blank cell value.

Marking this as Best Answer, but I appreciate the help from both of you. Thanks again!

Paul Newcome · 05/19/22

Happy to help. 👍️

Yes. Empty quotes "" is the same as blank. Not relevant to this particular solution but definitely very helpful as well is <> is the same as "not equal to" or the NOT function.

[Column Name]@row <> ""

is the same as

NOT(ISBLANK([Column Name]@row))

StevieSango · 05/19/22

Noted... and thanks again!

StevieSango · 05/19/22

Hi, Paul;

It looks like I spoke too soon. Apologies for the backtracking, but this one is still giving me problems. Your formula works beautifully for what I've been attempting (even when I've inserted cross-table INDEX MATCH values) if either [Date A] or [Date B] are blank, but if both are blank then I get an INVALID OPERATION error instead.

Any idea why that might be? I'm guessing its an issue with the OR function, but I haven't used it before. So far none of the straws I've grasped at have helped me solve this.

If you have any input, I'd appreciate it. Thanks...

Paul Newcome · 05/19/22

There are a few ways we can try to solve for this. Here is where I would start:

=IFERROR(IF(OR([Date A]@row = "", [Date B]@row = "", MAX([Date A]@row, [Date B]@row) > Deadline@row), "Red", "Green"), "Red")

StevieSango · 05/20/22

Thank you so much for sticking with me, Paul. I had to look up IFERROR once I saw your reply...

I changed the formula in the offending cell and received Red. Since I received an error before, I presume the Red result means there's an error. So, I did some experimenting. This is strange...

Here's a screenshot from the actual sheet--I need to do away with simple column names from my example for this, my apologies--these are the fields relevant to the current issue.

[SOP DATE] is the Deadline (I've also done an INDEX-MATCH here, which is my ultimate goal, and may be part of the problem). The two leftmost "G1" columns are Dates A and B. [N-36 STATUS] is the status result I've been trying to achieve.

Line 1 (I threw in some numbers at the left) was the line that clued me into the problem. It's using the IFERROR formula variant you provided:

=IFERROR(IF(OR([G1 TKO Dwg Level Agreed (NPPC)]@row = "", [G1 Basic Plan "Concept" & Assumptions (PE)]@row = "", (MAX([G1 TKO Dwg Level Agreed (NPPC)]@row, [G1 Basic Plan "Concept" & Assumptions (PE)]@row)) > (INDEX({N-36.ref}, MATCH($Project@row, {Project.ref}, 0)))), "Red", "Green"), "Red")

As you can see, the result is Red, so it would seem there's an error here somewhere. Just for fun I tried a couple of other variations.

Line 2 uses [SOP DATE] as the Deadline value, and returns a status as long as there's no actual Deadline value. Entering a date in [SOP DATE] causes an #INVALID OPERATION error when all other date values are blank. Here's the formula:

=IF(OR([G1 TKO Dwg Level Agreed (NPPC)]@row = "", [G1 Basic Plan "Concept" & Assumptions (PE)]@row = "", MAX([G1 TKO Dwg Level Agreed (NPPC)]@row, [G1 Basic Plan "Concept" & Assumptions (PE)]@row) > [SOP DATE]@row), "Red", "Green")

Lines 3, 4 and 5 use the INDEX-MATCH value, and return the error with all blank Deadline and date values. They will return a status if any of the three fields is populated with a date. Formula:

=IF(OR([G1 TKO Dwg Level Agreed (NPPC)]@row = "", [G1 Basic Plan "Concept" & Assumptions (PE)]@row = "", MAX([G1 TKO Dwg Level Agreed (NPPC)]@row, [G1 Basic Plan "Concept" & Assumptions (PE)]@row) > (INDEX({N-36.ref}, MATCH($Project@row, {Project.ref}, 0)))), "Red", "Green")

Line 6 was an experiment. I added the "G1" column on the right to see if it altered anything about the formula's behavior and it did not. Here's the formula if you're curious:

=IF(OR([G1 TKO Dwg Level Agreed (NPPC)]@row = "", [G1 Basic Plan "Concept" & Assumptions (PE)]@row = "", [G1 Comp Design Approved (PURCH)]@row = "", MAX([G1 TKO Dwg Level Agreed (NPPC)]@row, [G1 Basic Plan "Concept" & Assumptions (PE)]@row, [G1 Comp Design Approved (PURCH)]@row) > (INDEX({N-36.ref}, MATCH($Project@row, {Project.ref}, 0)))), "Red", "Green")

So it seems like there's an issue regardless of which source I use for the Deadline value, but the behavior changes depending on the source of that value. Since IFERROR tells me there's an error, where do I go from here? Is this a matter for Support now, or can I do more investigating on my own?

Thanks again for your help...

StevieSango · 05/20/22

I think I figured out at least part of my problem. This is from Smartsheet's instructions on MAX:

I'm using MAX in a Symbol column to get a status. No wonder it's throwing up on me. So... that means your solution wouldn't work, Paul. I don't know if that's my entire issue, but it has to be a major contributor.

I'm headed back to the drawing board...

StevieSango · 05/20/22