RGYB Ball Formula

Options

Hi there, Trying to create a formula where it uses date for red, yellow, green and then Complete checkbox for blue. Below is where I'm at, but it's not functioning. Any advice?


=IF(Finish@row - 3 <TODAY(), "Red", IF(Finish@row - 10< TODAY(), "Yellow", IF(Finish@row - 15< TODAY(), "Green", IFAND(Complete@row = 1,"Blue"))))]

Best Answer

Answers

  • Toby W
    Toby W ✭✭✭
    Options

    @Sean Morgan when i attempt this formula i get an error. #UNPARSEABLE. I dont need the "completed" checkbox. What i was looking for was a formula that would essentially say "If request date is less than 15 days, green, if less than 30 days, yellow, if less than 60, red, all others are blue"

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Toby W

    What is the formula you're currently working with?

    You could try something like this, depending on your end goal:

    =IF([Request Date]@row <= TODAY(-60), "Red", IF([Request Date]@row <= TODAY(-30), "Yellow", IF([Request Date]@row >= TODAY(-15), "Green")))


    Break out each statement to see what it says:

    =IF([Request Date]@row <= TODAY(-60), "Red",

    If the Date in the Request Date column is more than 60 days in the past, turn Red

    IF([Request Date]@row <= TODAY(-30), "Yellow",

    If the Date in the Request Date column is more than 30 days in the past, but less than 60 days in the past, turn Yellow

    IF([Request Date]@row >= TODAY(-15), "Green", "Blue")))

    If the Date in the Request Date column is between 15 days in the past, today, or in the future, return Green.

    Although there's a blue statement, we won't get to that statement as all dates are covered. If this isn't what you want to do, please write out exactly what you want for each status, with a screen capture example if possible.

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!