# Formula

Options

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
edited 05/30/24
Options

=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.

• Options

@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

• ✭✭✭✭✭✭
edited 05/30/24
Options

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.

• Options

Green is based on start date

• ✭✭✭✭✭✭
Options

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.

• Options

If the start date is less than 44 days from the Target end date project health is green

• ✭✭✭✭✭✭
edited 05/30/24
Options

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.

• ✭✭✭✭✭✭