Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
Task Health - Red, Yellow, Green
I am currently using =IF(TODAY() - [Due Date]@row < 0, "Green", IF(TODAY() > [Due Date]@row, "Red", IF(TODAY() - [Due Date]@row < [Processing Goal]@row, "Yellow"))) because I can't figure out how to get the yellow to work correctly.
I am looking for a formula that will display green if today is less than the due date, red if today is greater than the due date and yellow if today + 1/2 the processing goal is less than the due date. If the processing goal is 10 days, I want the health to turn yellow when we are 5 days from the due date so we can assign additional resources before we reach the due date. Can anyone help?
Best Answer
-
Give this a try:
=IF(TODAY() > [Due Date]@row, "Red", IF(TODAY(ROUND(VALUE([Processing Goal]@row) / 2)) < [Due Date]@row, "Green", "Yellow"))
If that doesn't work, how exactly is the Processing Goal column being populated?
Answers
-
Hello @blmccue!
Try the following formula:
=IF(TODAY() > [Due Date]@row, "Red", IF(TODAY([Processing Goal]@row / 2) < [Due Date]@row, "Green", "Yellow"))
If my comment helped you, please help others by marking it as an accepted answer and consider helping me by clicking the 💡Insightful or ❤️Awesome buttons below!
Monique Odom-Stearn
Pronouns: She/Her (What’s this?)
“Take chances, make mistakes, get messy!” – Ms. Frizzle -
Hi @Monique_Odom_Comcast, I had tried that once and tried again but receive and invalid data type error.
-
Wondering if anyone else has any suggestions?
I've also added a helper column that calculates 1/2 the processing goal to see if that would help fix the errors
=IF(TODAY() > [Due Date]@row, "Red", IF(TODAY(Helper@row) < [Due Date]@row, "Green", "Yellow"))
-
Give this a try:
=IF(TODAY() > [Due Date]@row, "Red", IF(TODAY(ROUND(VALUE([Processing Goal]@row) / 2)) < [Due Date]@row, "Green", "Yellow"))
If that doesn't work, how exactly is the Processing Goal column being populated?
-
Hi @blmccue,
The issue may be in trying to use a decimal inside TODAY(), that causes an "#invalid data type" error.
Hope that helps,
Dave
-
@Paul Newcome, that worked!
Greatly appreciated.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.9K Get Help
- 474 Global Discussions
- 207 Use Cases
- 517 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 83 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!