# IF Function and Cells without Data

I am trying to set up an IF function to automatically set a status using the RYGB symbols and how far away the target completion date is. I have had some success with the following

=IF([Target Completion Date]1 <= TODAY(-1), "Red", IF([Target Completion Date]1 <= TODAY(5), "Yellow", IF([Target Completion Date]1 >= TODAY(6), "Green", "Blue")))

Where I am getting stuck now is that if the Target Completion Date has no data in it, it's automatically turning the status column to Red and not Blue.

• ✭✭✭✭✭✭

Try:

=IF(ISDATE([Target Completion Date]@row), IF([Target Completion Date]@row <= TODAY(-1), "Red", IF([Target Completion Date]@row <= TODAY(5), "Yellow", IF([Target Completion Date]@row >= TODAY(6), "Green", "Blue"))),"Blue")

Work?

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• ✭✭✭✭✭✭

Jaclyn,

You could also try this:

=IF(ISBLANK([Target Completion Date}@row, "Blue",IF([Target Completion Date]@row <= TODAY(-1), "Red", IF([Target Completion Date]@row <= TODAY(5), "Yellow", "Green")))

Best,

Heather

• Thank you, this worked!! This will definitely help with tracking our progress.

One additional question, is there a way to add a dependency on another row that If Actual Completion Date is a date then blank? I couldn't quite get the right string for all of it and wasn't sure if it was possible.

• ✭✭✭✭✭✭

Jaclyn,

To tell a cell to be blank if something is true, use "" as the result. So, for example:

=IF(ISDATE([actual completion date]@row),"")

This statement could be nested in with the other if statements like this:

=IF(ISDATE([Actual Completion Date]@row),"",IF(ISBLANK([Target Completion Date}@row, "Blue",IF([Target Completion Date]@row <= TODAY(-1), "Red", IF([Target Completion Date]@row <= TODAY(5), "Yellow", "Green"))))

Best,

Heather

• That string didn't quite work, I received an #UNPARSEABLE message. I'll try and address throughout the day though, thank you for your quick response!

• ✭✭✭✭✭✭

Ok - try this one instead:

=IF(NOT(ISBLANK([Actual Completion Date]@row))"",IF(ISBLANK([Target Completion Date}@row, "Blue",IF([Target Completion Date]@row <= TODAY(-1), "Red", IF([Target Completion Date]@row <= TODAY(5), "Yellow", "Green"))))

• Still the same result. I used Mark's formula in the first response to my initial question for the status updates, which may be playing into it. Thank you for your help, I'm still new to formulas and I'm sure I'll be posting here again.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!