Formula
I need to create 2 formulas for my project plan:
- When target end date is 45 days out, turn Health to orange
- When target end date is 15 days out, turn Health to red
Best Answer
-
IF You want it to fall in of 44 days before up to 1 day before then use this formula
=IF(And([Start Date]@row >[Target End Date]@row-44,[Start Date]@row <[Target End Date]@row),"Green",IF([Target End Date]@row <= Today(15), "Red",IF([Target End Date]@row <= Today(45),"Yellow")))
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
Answers
-
=IF([Target End Date]@row <= Today(15), "Red",IF([Target End Date]@row <= Today(45),"Yellow")
What is the rule to make it Green? I can build the entire formula for you then.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
@Mark.poole There is no rule to make it green, I just made all the columns green manually. Here is a snippet with filled-in dates. I appreciate your help
-
No I was asking what criteria are you using to make it green? So I can add that criteria to the Formula for you. Is It if the End Date is so far out. Is it. If its started at a certain time frame. Or finished before a certain time frame?
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
Green is based on start date
-
Ok thank you. Last question. How so? Is it that the start date is so many days away from today? And if so how many? If start date is at any point that’s outside of the 45 day range?
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
If the start date is less than 44 days from the Target end date project health is green
-
Based on the information provided
=IF([Start Date]@row <[Target End Date]@row-44,"Green",IF([Target End Date]@row <= Today(15), "Red",IF([Target End Date]@row <= Today(45),"Yellow")))
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
IF You want it to fall in of 44 days before up to 1 day before then use this formula
=IF(And([Start Date]@row >[Target End Date]@row-44,[Start Date]@row <[Target End Date]@row),"Green",IF([Target End Date]@row <= Today(15), "Red",IF([Target End Date]@row <= Today(45),"Yellow")))
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
