# RGYB Ball Formula

Options
edited 05/15/20

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"))))]

• ✭✭✭
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"

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!