Welcome!

IF AND ISBLANK formula

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.

Find more posts tagged with

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Changing cell value automatically based on form responses
I have a form that will be filled out internally. There's a cell labeled "Department" which contains 8 dropdown list values. There's a another cell called "Executives" which are the leaders of these departments. In the form, I'm attempting to do a logic where when you choose a specific "Department", then the cell…
Limiting Access to Folder and it's assets
I shared access to a user to a specific folder under workspace A (folder is 2025-09-Service Factory). However, I can see that he's shown to have access to all the other child folders under 02-ACTIVE Initiatives. Why is that when I only shared out one folder? Is there a way to only share access to users to ONLY specific…
existing files can't be open after change my plan to Pro
what i can do if all my existing smartsheet gant files can't be open after change my plan to Pro? what could be the reason ?