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

08/14/19 Edited 12/09/19

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!

 

sheet.JPG

Green.JPG

yellow and Red.JPG

Comments

  • Paul NewcomePaul 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)

    thinkspi.com

  • AlejandraAlejandra Employee

    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!

  • AlejandraAlejandra Employee

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

  • Paul NewcomePaul Newcome ✭✭✭✭✭

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

    thinkspi.com

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

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! yes

    thinkspi.com

  • 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åAndrée Starå ✭✭✭✭✭

    Hi @bentlb3

    I hope you're well and safe!

    Try something like this.

    =IF([email protected] = "", "Not Started", IF([email protected] <> "", "In Progress"))
    

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

    =IF([email protected] = "", "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!

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Thank you! That worked perfectly. I need to add in a separate column now to the formula where if the value in that [email protected] is "Yes", then make the value "Complete".

    I tried adding to your formula and entering: =IF([email protected] = "", "Not Started", "In Progress", IF(AND(Task [email protected] = "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å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([email protected] = "", "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!

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Worked perfect Andree! Thank you so much!

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    @bentlb3

    Excellent!

    You're more than welcome!

    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!

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Sign In or Register to comment.