Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Nesting if functions for blank cells

Options
Laryssa
Laryssa
edited 12/09/19 in Archived 2017 Posts

I'm trying to create a formula with the RYB balls that says if two date columns (Start and Completed) are blank, then the value should be a red ball. If there is a date in the start column but not in the completed column, then the value should be a yellow ball. If both columns have date, then the value should be a green ball. I got as far as the formula below, but am getting an unparseable error. Any help would be appreciated!

=IF(AND(ISBLANK([Start Date]1), ISBLANK([Completed Date]1)), "Red"), IF(AND(ISBLANK([Completed Date]1), NOT(ISBLANK([Start Date]1))), "Yellow")

Comments

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    This one works. 

    =IF(AND(ISBLANK([Start Date]1), ISBLANK([Completed Date]1)), "Red", IF(AND(ISBLANK([Completed Date]1), ISDATE([Start Date]1)), "Yellow", "Green"))

    However, if you have a date in the completed without a start date you get a green cell... that may be fine, but you will have to add additional IF statements to check for that if you need it to cast a different message. :) 

  • Laryssa
    Options

    That worked perfectly - Thank you! There wouldn't be a start date without a completed date, so that shouldn't be a problem. Thanks again!

This discussion has been closed.