Formula if cell is blank then, if cell is not blank then....

Formula gods, looking for help, please.

I'm trying to write a formula that says if the finish date is not blank, then the QA status is Green, if finish date is blank, then go to the days open field and if less than >= 60 and <=89 then Yellow, if days open is >=90 then Red.

I can write the individual formulas to get the red to work and an indivudual formula to get the yellow and green to work, but not work together as one string.

Help!

Many thanks!

yellow and Red.JPG

Find more posts tagged with

Manufacturing

Formulas

Comments

Paul Newcome

You are actually very close already and have the right idea established. The same way you nested the "Red" at the end of the "Yellow"... You would take that nested IF and nest it after "Green".

=IF(NOT(ISBLANK([Finish Date]@row)), "Green", nested_if_for_yellow_and_red)

Alejandra

Hi Melissa,

It looks like you were really close to getting your formula to work. If you received an #INCORRECT ARGUMENT SET error it's because the last portion of your formula begins with IF(AND( but only one logical expression was given.

Here's what your formula should look like:

=IF(NOT(ISBLANK([Finish Date]@row)), "Green", IF(AND([Days Open]@row >= 60, [Days Open]@row <= 89), "Yellow", IF([Days Open]@row >= 90, "Red")))

I also noticed that if the finished date is blank, but the number of days open is less than 60, nothing is returned. If you would like to return a green symbol when the finish date is blank, but the number of days open is less than 60, you could use the formula below:

=IF(NOT(ISBLANK([Finish Date]@row)), "Green", IF([Days Open]@row < 60, "Green", IF(AND([Days Open]@row >= 60, [Days Open]@row <= 89), "Yellow", IF([Days Open]@row >= 90, "Red"))))

Hope this helped!

Alejandra

I posted my response a couple minutes too late. Thanks for chiming in Paul!

Paul Newcome

Haha. No worries. That seems to happen a lot here in the Community.

Melissa Shoumlisky

That did it! You all are awesome! Thank you so much!

Paul Newcome

Happy to help!

bentlb3

I have a similar scenario, but I'd like to return a value in a cell based on a date field at that row. I got this to work as far as =IF(NOT(ISBLANK([Date]@row)), "Not Started"...thanks to your help above.

Now, how do I add a condition where if the "Date" column has a date entered (i.e. is not blank), then the return is "In Progress", to the above formula?

Thank you!

Andrée Starå

Hi @bentlb3

I hope you're well and safe!

Try something like this.

=IF(Date@row = "", "Not Started", IF(Date@row <> "", "In Progress"))

You could also simplify it if it works for your need.

=IF(Date@row = "", "Not Started", "In Progress")

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

bentlb3

Thank you! That worked perfectly. I need to add in a separate column now to the formula where if the value in that cell@row is "Yes", then make the value "Complete".

I tried adding to your formula and entering: =IF(Date@row = "", "Not Started", "In Progress", IF(AND(Task Closed?@row = "Yes", "Complete"), but no luck and get the #UNPARSEABLE error.

Any solution to factor in the second criteria to enable the "Complete" value return?

Thank you!

Andrée Starå

@bentlb3

Excellent!

You're more than welcome!

Try something like this for the added criteria.

=
IF([Task Closed?]@row = "Yes", "Complete", 
IF(Dates@row = "", "Not Started", "In Progress"))

Did that work?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

bentlb3

Worked perfect Andree! Thank you so much!

Andrée Starå

@bentlb3

Excellent!

You're more than welcome!

Sign in to join the conversation!

Quick Links

Trending discussions

index match using multiple critieria
Hello, I want to use a formula using INDEX/ MATCH Function using multiple criteria to get a single value I am trying to get a value of Column Description from File 2 based on matching Source System ,Table name and Attribute Field Index ( as the column names may be different ) The formula Im using gets me an INVALID…
How can I use Conditional Formatting for date in the past or future when a formula created the date
Hi Everyone, Thank you for your time in reading this. How can I use Conditional Formatting for date in the past or future when a formula created the date? The black columns are different dates generated. The 2 yellow columns are then selecting the relevant date based on different criteria. The white columns are me trying…
Auto Check a box after 24 hours
I have tried to get smartsheet to auto check a box 24 hours after a row is created and column "Task Started" is not checked. =IF(AND([Task Started]@row <> 1, TODAY() - Created@row >= 1), 1) This returns #UNPARSEABLE. Any help would be appreciated.