IF AND ISBLANK formula

Jgrinde27711

Hi, I want a RGY to turn Red if we are "pass" our target date,

yellow if "at" the target date, and Green if "before" our target date, 

=IF(ISBLANK([Target Date]1), "", IF([Target Date]1 > TODAY() + 1, "Green", IF([Target Date]1 >= TODAY(), "Yellow", IF([Target Date]1 < TODAY(), "Red", ""))))

this formula works, except even if we finish on time because I use TODAY() it will still turn red, I need a AND condition if "WO# Finish" is TRUE it stops updating. 

 

I tried the code below and many different ways but can't get it to work, just wondering if someone can help me out, 

=IF(AND(ISBLANK([Target Date]1, ""), [WO# Finish]1 = false), IF([Target Date]1 > TODAY() + 1, "Green", IF([Target Date]1 >= TODAY(), "Yellow", IF([Target Date]1 < TODAY(), "Red", "")))))

Thanks. 

p_bureau

Hi,

 

what do you mean by "Stop updating" ? Do you mean keep the current value of the RYB when "WO# Finish" is TRUE ? Or just make the RYB blank ?

 

If you want the last option, then the formula should be :

=IF(OR(ISBLANK([Target Date]1, [WO# Finish]1 = TRUE),"", IF([Target Date]1 > TODAY() + 1, "Green", IF([Target Date]1 >= TODAY(), "Yellow", "Red")))

 

Does this help ?

 

Best regards,

 

Paul.

 

Jgrinde27711

I want to keep the current value of RYB

p_bureau

Well I'm pretty sure this can't be done directly by Smartsheet (like if could be by a macro in Excel).

 

You can't refer to a value in the past, and you can't "freeze" a value without manual input.

 

Best regards,

 

Paul.

Jgrinde27711

I kind a figure that it would not work.

what I have done is created a second column if the user puts the check mark WO Completed it takes the modified date into that column and stores it there, as long as no body modifies the row it works. 

thanks.