IF Function and Cells without Data

Jaclyn Reilly

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.

Thank you in advance.

Mark Cronk

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

Heather Duff

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

Jaclyn Reilly

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.

Heather Duff

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

Jaclyn Reilly

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!

Heather Duff

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

Jaclyn Reilly

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.