RAG status formula using TODAY and end date

Hey smartsheet community, any help fixing this RAG status formula?

I am trying to automatically show RYGG balls dependant on the end date and today's date. With the exception of cancelled and complete tasks;

any task with the end date TODAY or in the future should be green
any task up to 3 working days in the past should be yellow
and any task over 3 working days in the past should be red

My current formula seems to work for green, yellow, gray and cancelled - but not for red. It also doesn't take into account working days.

=IF(Status@row = "Complete", "Gray", IF(Status@row = "Cancelled", "N/A", IF(TODAY() = [End Date]@row, "Green", IF([End Date]@row - TODAY() < 0, "Yellow", IF([End Date]@row - TODAY(-3) < 0, "Red", "Green")))))

Any help would be amazing!!

Find more posts tagged with

Formulas

Project Management

Accepted answers

All comments

L_123

=IF(Status@row = "Complete", "Gray", IF(Status@row = "Cancelled", "N/A", IF(TODAY() >= [End Date]@row, "Green", IF([End Date]@row >=today()-3, "Yellow","Red"

Give that a try

Anna Keeton

Hi thanks for the help - though it doesn't seem to have quite worked yet.

With a bit of twiddling I've got: =IF(Status@row = "Complete", "Gray", IF(Status@row = "Cancelled", "N/A", IF(TODAY() = [End Date]@row, "Green", IF([End Date]@row = TODAY(-3), "Yellow", IF([End Date]@row <= TODAY(-4), "Red")))))

But future tasks are not showing as green, and the formula still seems to use net days rather than working days - any suggestion on how to work around those 2 bits?

Thanks a bunch!

L_123

My bad I got a sign mixed up.

=IF(Status@row = "Complete", "Gray", IF(Status@row = "Cancelled", "N/A", IF([End Date]@row >= TODAY(), "Green", IF([End Date]@row >= TODAY() - 3, "Yellow", "Red"))))

Yours only has equal values, not ranges, so it wouldn't for example check if the end date was yesterday.

Anna Keeton

Thanks @L@123 that seems to have worked better! thank you :)

The only thing that's missing, is setting the formula to use working days. i.e at the moment 2 working days ago is showing as red, but it should be yellow

Is there a way to make the calcualtion based on working days (Mon to Fri)?

Ivelina Ivanova

Thank you, all, for being so helpful.

I want to add something more to this formula:

=IF(Status@row = "Complete", "Gray", IF(Status@row = "Cancelled", "N/A", IF([End Date]@row >= TODAY(), "Green", IF([End Date]@row >= TODAY() - 3, "Yellow", "Red"))))

I have 4 RAG statuses (green, yellow, red, blue). I want to have a "blue" status if there is no end date in the column. That will be a sign to me I need to define the end day.

Thank you for your help.

L_123

=IF(Status@row = "Complete", "Gray", IF(Status@row = "Cancelled", "N/A", if(isblank([End Date]@row),"Blue", IF([End Date]@row >= TODAY(), "Green", IF([End Date]@row >= TODAY() - 3, "Yellow", "Red"

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.