Automate RYG Balls Based on Two Different Dates

Options
Hannah Bell
Hannah Bell ✭✭
in Formulas and Functions

I am trying to create a formula that looks at two different start dates to set the project status. It looks at the first start date to see how far out (TODAY) is from that date and looks to see if there is a date in the second start date. This is what I have so far:

=IF(AND([Start Date]17 >= TODAY(+28), ISBLANK([Start Date]18)), "Green"), IF(AND([Start Date]17 <=TODAY (+28), ISBLANK([Start Date]18)), "Yellow"), IF(AND([Start Date]17 <=TODAY (+14), ISBLANK([Start Date]18)), "Red"

I also need to set a condition that if there is a date in [Start Date]18 then the project status changes to "Blue".

Any help would be appreciated. Thank you!

Tags:
· Share on FacebookShare on Twitter

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    Try:

    =IFERROR(IF(ISDATE( [start date]18), "Blue", IF([ Start Date]17 >= TODAY(+28), "Green", IF([Start Date]17 >=TODAY (+14), "Yellow", "Red"))),"")

    If [start date]18 is a date then it responds "blue" so there's no need to check that cell in the rest of the formula. I simplified your logic since IF statements go in order until they find a true response.

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

    · Share on FacebookShare on Twitter

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    Try:

    =IFERROR(IF(ISDATE( [start date]18), "Blue", IF([ Start Date]17 >= TODAY(+28), "Green", IF([Start Date]17 >=TODAY (+14), "Yellow", "Red"))),"")

    If [start date]18 is a date then it responds "blue" so there's no need to check that cell in the rest of the formula. I simplified your logic since IF statements go in order until they find a true response.

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

    · Share on FacebookShare on Twitter
  • Hannah Bell
    Hannah Bell ✭✭
    Options

    Mark - that did work! Thank you for your help!

    · Share on FacebookShare on Twitter
  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Hi Hannah, Happy to help. Glad you found a solution. Thank you for contributing to the Community.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!