I am trying to create a formula with Symbols based on date, today, and range
![Brett L](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
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”)))
Answers
-
Are you wanting to flag based on the Tentative Discharge Date being in the past or in the future?
-
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”))))
-
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)
-
Thank you Michael for taking the time to improve upon my formula, unfortunately I am still getting the dreaded #unparsable.
-
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"))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!