I am trying to create a formula with Symbols based on date, today, and range

Brett L

I am wanting to build a formula with the following features.

Blue= tentative discharge date (date inputted in cell)

Green = tentative discharge 7 days from today

Yellow = tentative discharge date of 8 – 14 days from today

Red = tentative discharge date of 15 days or more from today

This is what I have proposed, but not luck:

=IF(ISDATE([Tentative Discharge Date]1, "Blue", IF(AND[Tentative Discharge Date]1 =< TODAY(), "Green", IF(AND([Tentative Discharge Date]1 >= TODAY(-7), [Tentative Discharge Date]1 <= TODAY(-14)), "Yellow", IF([Tentative Discharge Date]1 > TODAY(-15), “Red”))))

or

=IF(ISDATE([Tentative Discharge Date]1), "Blue", IF(Today(-7)>=[Tentative Discharge Date]1,"Green", IF(TODAY(-8) >=  [Tentative Discharge Date]1, TODAY(-14)>= [Tentative Discharge Date]1), "Yellow", IF(TODAY(-15) >[Tentative Discharge Date] 1, “Red”)))

Paul Newcome

Are you wanting to flag based on the Tentative Discharge Date being in the past or in the future?

Brett L

First of all, thank you Paul for taking a look at this! I have seen your name responding in quite a few discussions.

I may have missed the mark for trying to explain what I am trying to accomplish.

Two columns:

1)     Status with Colors

2)     Tentative Discharge Date with Blank or Date

IF a date is in tentative discharge date cell, status will = Blue

If today is greater than the tentative discharge date by 1-6 days (in the past), status will = green

If today is greater than the tentative discharge date by, and between 8-14 day (in the past), status will = Yellow

If today is greater than the tentative discharge date by more than 15 days (in the past), status will = Red

=IF([Tentative Discharge Date]@row = "", IF([Tentative Discharge Date]@row < TODAY(15), "Green", IF(AND([Tentative Discharge Date]@row <= TODAY(14), [Tentative Discharge Date]@row <= TODAY(7)), "Yellow", IF([Tentative Discharge Date]@row < TODAY(), "Red", ”Blue”))))

=IF([Tentative Discharge Date]@row = "",

 IF([Tentative Discharge Date]@row < TODAY(15), "Red",

IF(AND([Tentative Discharge Date]@row <= TODAY(14), [Tentative Discharge Date]@row <= TODAY(8)), "Yellow",

IF([Tentative Discharge Date]@row < TODAY(), "Green",

”Blue”))))

Michael Culley

A few things I've noticed in your formula.

The first line:

=IF([Tentative Discharge Date]@row = "",

If the date is blank, I assume you want the status to be blank, so that would be:

=IF([Tentative Discharge Date]@row = "","",

Secondly I noticed this part:

IF(AND([Tentative Discharge Date]@row <= TODAY(14), [Tentative Discharge Date]@row <= TODAY(8)), "Yellow"

Shouldn't it be [Tentative Discharge Date]@row >= TODAY(8)) (Greater than instead of less than)

Brett L

Thank you Michael for taking the time to improve upon my formula, unfortunately I am still getting the dreaded #unparsable.

Paul Newcome

The reason for the error is because of the quotes around "Blue". Notice how those are slanted in your formula but straight up and down everywhere else? Those slanted quotes are called "Smart Quotes" which (ironically enough) are not recognized as valid characters in Smartsheet. Retyping those here in the Community, directly in Smartsheet, or in a text editor such as Notepad (not Word) should correct that.

Even then though I am not sure your formula would work as intended. Give the below a try...

=IF([Tentative Discharge Date]@row = "", "", IF([Tentative Discharge Date]@row < TODAY(-15), "Red", IF([Tentative Discharge Date]@row <= TODAY(-8), "Yellow", IF([Tentative Discharge Date]@row < TODAY(), "Green", ”Blue"))))