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

Best Answer

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Answer ✓
    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.

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 05/30/24
    Options

    @Mdonan

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

  • Mdonan
    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

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    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.

  • Mdonan
    Options

    Green is based on start date

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    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.

  • Mdonan
    Options

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

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    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.

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Answer ✓
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!