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

Find more posts tagged with

Formulas

IF(AND

Accepted answers

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!

All comments

Cheryl C

Hi Carmen

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

kowal

hi,

try this

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.