IF AND OR Formula *almost* works correctly but one part!
Hiya folks- all arguments work correctly except turning Health the color Yellow when Recommended Due Date is within 3 days of current date and status is not Complete.
Here is what I need to accomplish:
IF [Status] is"Complete" or "Cancelled/NA" then [Health] ="Blue"
IF [Recommended DueDate] <= TODAY and [status] <> "complete" then [health] ="red"
IF [Recommended DueDate] <= TODAY(-3) and [status] <> "complete" then [health]= "yellow"
IF none of the aboveconditions exist [Health] = "green"
Current and almost accurate formula:
=IF(OR(Status@row = "Complete", Status@row = "Cancelled/NA"), "Blue", IF(AND([Recommended Due Date]@row <= TODAY(), Status@row <> "Complete"), "Red", IF(AND([Recommended Due Date]@row <= TODAY(-3), Status@row <> "Complete"), "Yellow", "Green")))
Note: I did try changing the last "AND" to an "OR" and it turned it yellow BUT it also turned other things yellow that it should not have.
Answers
-
First thing to note is that since you already have the "Complete" argument taken care of in the first IF, you don't need to specify anything about that in the following IFs.
The issue with your yellow is a misstatement. What you want and what you are typing are two different things.
"Recommended Due Date is within 3 days of current date" would be less than or equal to 3 days in the future, but your formula is looking for dates that are more than 3 days past which is already covered by the Red argument. Try changing the -3 to 3 and see if that works for you.
-
@Paul Newcome You, sir, are a scholar and a gentleman—That did the trick! I'm both humbled and grateful for the help and the tip (AI wrote the formula but noted). Thank you again!
-
Happy to help. 👍️
-
@Paul Newcome Happy Friday. New Roadblock as it relates to the health - Want one field to show Overall Project Health. Ideally, we would figure out how to average the colors and/or tasks past due to return RYBG for the overall projects (about ~230) so that we can report it on a Dashboard. Any ideas on formula would be so appreciated.
Circular reference error formula in OPH row: =IF(Level@row = 0, =AVG(Health:Health), "")
Blocked error Health row formula: =IF(Summary@row = 1, "", IF(Level@row = 0, [Overall Project Health]@row, IF(OR(Status@row = "Complete", Status@row = "Cancelled/NA"), "Blue", IF(AND([Recommended Due Date]@row <= TODAY(), Status@row <> "Complete"), "Red", IF(AND([Recommended Due Date]@row <= TODAY(-3), Status@row <> "Complete"), "Yellow", "Green")))))
Grateful for any guidance you or others may have.
-
How exactly would you want the average to be calculated? Would certain colors be weighted such as Red counts as two but Green ounts as one, or some other logic?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!