Using Multiple IFBLANK Checks With IF

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...

Find more posts tagged with

Accepted answers

Paul Newcome

Try this:

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

one is blank

the other is blank

either are past the deadline

then "Red"

otherwise "Green"

StevieSango

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

All comments

James Keuning

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

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

Try this:

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

one is blank

the other is blank

either are past the deadline

then "Red"

otherwise "Green"

StevieSango

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

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

Noted... and thanks again!

StevieSango

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

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

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:

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

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

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:

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")

Edit: typo

Paul Newcome

The only time you have to use MAX in a date column while referencing dates is if you are outputting a date. In this case you are not outputting a date. You are just pulling a date for further calculation. I do this regularly with no issue.

I think the problem is coming from having the MAX inside of the OR. I have run into this a couple of times in the past. Splitting them out into their own IF statements should work.

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.