Status Indicator formulas
I have been working on this for two days and I finally decided to ask for help. I am trying to achieve the following:
If Dev Progress is marked as "complete" then "Blue"
If Dev Progress is marked as "In progress" and Today is between Start date and End date then "Green"
If Dev Progress is marked as "not started" and Today is greater than the start date but less than the end date, mark as yellow
If Dev Progress is marked as "delayed" then "Red"
If Dev Progress is marked "not started" and/or the end date is greater than Today mark as "Red"
If Dev progress is marked as "in Progress" and Today is greater than the end date mark as "Red"
If Dev is marked as "Not Applicable" then show "N/A"
Below is what I have tried so far and it's already not working. I haven't added all the parts of the formula.
=IF([Dev Progress]3 = "Completed", "Blue", IF([Dev Progress]3 = "In Progress", [Dev Start Date]3 < TODAY(), [Dev End Date]3 > TODAY()), "Yellow", IF(OR(AND([Dev Progress]3 = "Not Started"), AND([Dev Start Date]3 < TODAY(), OR(AND([Dev Progress]3 = "In Progress", [Dev End Date]3 < TODAY())))), "Red"))
I tried using the above and modifying it by clicking the cell(@row)
Thank you!
Best Answers
-
Try this as your formula.
=IF([Dev Progress]@row = "Completed", "Blue", IF(AND([Dev Progress]@row = "In Progress", [Dev Start Date]@row < TODAY(), [Dev End Date]@row > TODAY()), "Green", IF(AND([Dev Progress]@row = "Not Started", [Dev Start Date]@row < TODAY(), [Dev End Date]@row > TODAY()), "Yellow", IF([Dev Progress]@row = "Delayed", "Red", IF(OR([Dev Progress]@row = "Not Started", [Dev End Date]@row > TODAY()), "Purple", IF([Dev Progress]@row = "Not Applicable", "N/A", ""))))))
I also changed one of the "reds" to purple just to make testing easier.
In "Dev Progress" these are the values that I used. Completed, In Progress, Not Started, Delayed, and Not Applicable.
And here is a shot of all of the cells.
Hope this helps!
Heath Hilton
-
I think it isn't liking the fact that you are looking for "text" in a "date" cell (not 100% though). I also wonder if it was a bit of the order at which it was seeing the order within the formula.
Try this one, I also added another condition for the line that I missed above (If Dev progress is marked as "in Progress" and Today is greater than the end date mark as "Red").
=IF([Dev Start Date]@row = "", "N/A", IF(AND([Dev Progress]@row = "In Progress", [Dev Start Date]@row < TODAY(), [Dev End Date]@row > TODAY()), "Green", IF(AND([Dev Progress]@row = "Not Started", [Dev Start Date]@row < TODAY(), [Dev End Date]@row > TODAY()), "Yellow", IF([Dev Progress]@row = "Delayed", "Red", IF(OR([Dev Progress]@row = "Not Started", [Dev End Date]@row > TODAY()), "Purple", IF(AND([Dev Progress]@row = "In Progress", [Dev End Date]@row < TODAY()), "Black", IF([Dev Progress]@row = "Not Applicable", "N/A", IF([Dev Progress]@row = "Completed", "Blue"))))))))
Hope this helps!
Heath Hilton
Answers
-
Try this as your formula.
=IF([Dev Progress]@row = "Completed", "Blue", IF(AND([Dev Progress]@row = "In Progress", [Dev Start Date]@row < TODAY(), [Dev End Date]@row > TODAY()), "Green", IF(AND([Dev Progress]@row = "Not Started", [Dev Start Date]@row < TODAY(), [Dev End Date]@row > TODAY()), "Yellow", IF([Dev Progress]@row = "Delayed", "Red", IF(OR([Dev Progress]@row = "Not Started", [Dev End Date]@row > TODAY()), "Purple", IF([Dev Progress]@row = "Not Applicable", "N/A", ""))))))
I also changed one of the "reds" to purple just to make testing easier.
In "Dev Progress" these are the values that I used. Completed, In Progress, Not Started, Delayed, and Not Applicable.
And here is a shot of all of the cells.
Hope this helps!
Heath Hilton
-
Hi Heath. Thank you that worked perfectly. I added some additional parameters I forgot and everything is working magically.
I tried adding another Not applicable. There are cases where I will not have a start and/or end date.
I added the following line:
If([Dev Start Date]@row = "not applicable", "N/A")
the formula works by itself but when I nest it into the bigger formula it does not work.
It returns a result of "invalid" could you assist?
-
I think it isn't liking the fact that you are looking for "text" in a "date" cell (not 100% though). I also wonder if it was a bit of the order at which it was seeing the order within the formula.
Try this one, I also added another condition for the line that I missed above (If Dev progress is marked as "in Progress" and Today is greater than the end date mark as "Red").
=IF([Dev Start Date]@row = "", "N/A", IF(AND([Dev Progress]@row = "In Progress", [Dev Start Date]@row < TODAY(), [Dev End Date]@row > TODAY()), "Green", IF(AND([Dev Progress]@row = "Not Started", [Dev Start Date]@row < TODAY(), [Dev End Date]@row > TODAY()), "Yellow", IF([Dev Progress]@row = "Delayed", "Red", IF(OR([Dev Progress]@row = "Not Started", [Dev End Date]@row > TODAY()), "Purple", IF(AND([Dev Progress]@row = "In Progress", [Dev End Date]@row < TODAY()), "Black", IF([Dev Progress]@row = "Not Applicable", "N/A", IF([Dev Progress]@row = "Completed", "Blue"))))))))
Hope this helps!
Heath Hilton
-
Yep that worked. Thank you so much
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!