Formula for RAG rating based on status and dates

Carmen H

Hi Smartsheet community,

I'm after a formula that will produce the different RAG ratings.

I'm definining them based on whether they are on schedule and their statuses.

Blue is 'Status'="Complete"

Red is (open for advisory)

• 'Status'='Not started' and 'start date' < today and end date is within 14 days OR
• Status= not started and end date<today OR
• 'Status' = 'in progress' and 'end date' is within 7 days

Amber (open for advice)

• 'Status' = in progress and end date is between 14-28 days
• Status = Not started and start date < today and end date is greater than 28 days

Green (open for advice) - not sure how we can show on track?

• Status is in progress and end date > 28 days
• Status = not started and start date > today

Cheryl C

Hi Carmen

If you want this as a column formula, try the formula below.

=IF([Status]@row = "Complete", "Blue",
IF(OR(AND([Status]@row = "Not started", [Start Date]@row < TODAY(), [End Date]@row <= TODAY() + 14),
AND([Status]@row = "Not started", [End Date]@row < TODAY()),
AND([Status]@row = "In progress", [End Date]@row <= TODAY() + 7)), "Red",
IF(OR(AND([Status]@row = "In progress", [End Date]@row > TODAY() + 14, [End Date]@row <= TODAY() + 28),
AND([Status]@row = "Not started", [Start Date]@row < TODAY(), [End Date]@row > TODAY() + 28)), "Amber",
IF(OR(AND([Status]@row = "In progress", [End Date]@row > TODAY() + 28),
AND([Status]@row = "Not started", [Start Date]@row > TODAY())), "Green", "No RAG Assigned"))))

kowal

hi,

try this

=IF(Status@row = "Complete", "Blue", IF(AND(Status@row = "Not Started", TODAY() >= [Start Date]@row, TODAY() <= [End Date]@row + 14), "Red", IF(AND(Status@row = "Not Started", TODAY() >= [End Date]@row), "Red", IF(AND(Status@row = "In Progress", TODAY() <= [End Date]@row + 7), "Red", IF(AND(Status@row = "In Progress", TODAY() <= [End Date]@row + 28, TODAY() >= [End Date]@row + 14), "Yellow", IF(AND(Status@row = "Not Started", TODAY() >= [Start Date]@row, TODAY() <= [End Date]@row + 28), "Yellow", IF(AND(Status@row = "In Progress", TODAY() >= [End Date]@row + 28), "Green", IF(AND(Status@row = "Not Started", TODAY() < [Start Date]@row), "Green", ""))))))))

Carmen H

@kowal @Cheryl C thank you both! The formula works but how would i make it so it shows in the coloured dots?

Update: all good I figured i just had to edit the format of the column. Thank you!

Cheryl C

Hi Carmen

If you want this as a column formula, try the formula below.

=IF([Status]@row = "Complete", "Blue",
IF(OR(AND([Status]@row = "Not started", [Start Date]@row < TODAY(), [End Date]@row <= TODAY() + 14),
AND([Status]@row = "Not started", [End Date]@row < TODAY()),
AND([Status]@row = "In progress", [End Date]@row <= TODAY() + 7)), "Red",
IF(OR(AND([Status]@row = "In progress", [End Date]@row > TODAY() + 14, [End Date]@row <= TODAY() + 28),
AND([Status]@row = "Not started", [Start Date]@row < TODAY(), [End Date]@row > TODAY() + 28)), "Amber",
IF(OR(AND([Status]@row = "In progress", [End Date]@row > TODAY() + 28),
AND([Status]@row = "Not started", [Start Date]@row > TODAY())), "Green", "No RAG Assigned"))))

kowal

hi,

try this

=IF(Status@row = "Complete", "Blue", IF(AND(Status@row = "Not Started", TODAY() >= [Start Date]@row, TODAY() <= [End Date]@row + 14), "Red", IF(AND(Status@row = "Not Started", TODAY() >= [End Date]@row), "Red", IF(AND(Status@row = "In Progress", TODAY() <= [End Date]@row + 7), "Red", IF(AND(Status@row = "In Progress", TODAY() <= [End Date]@row + 28, TODAY() >= [End Date]@row + 14), "Yellow", IF(AND(Status@row = "Not Started", TODAY() >= [Start Date]@row, TODAY() <= [End Date]@row + 28), "Yellow", IF(AND(Status@row = "In Progress", TODAY() >= [End Date]@row + 28), "Green", IF(AND(Status@row = "Not Started", TODAY() < [Start Date]@row), "Green", ""))))))))

Carmen H

@kowal @Cheryl C thank you both! The formula works but how would i make it so it shows in the coloured dots?

Update: all good I figured i just had to edit the format of the column. Thank you!

kowal

you welcome.