RAG Looking at Two Dates

SS Community,

I am hoping you can help with a formula problem I am trying to solve. I have a RAG column that is currently looking at a due date and based on some criteria it will return a RAG value. What I am trying to add to the formula when there is a revised due date added to look at that column instead and return the RAG based on the revised due date. Not all lines have a revised. due date

Here is my original formula:

=iF(Status@row = "Closed", "", IF([Due Date]@row < TODAY(), "Red", IF([Due Date]@row <= TODAY(7), "Yellow", IF([Due Date]@row >= TODAY(8), "Green"))))

The new column added is called 'Revised Due Date'

Is this formula possible?

Thank you in advance for any guidance.

All the best,

Carl Vieira

Find more posts tagged with

Accepted answers

Anupriya

You can try something on these lines -

=iF(Status@row = "Closed", "", IF([Revised Due Date]@row<>"", IF([Revised Due Date]@row < TODAY(), "Red", IF([Revised Due Date]@row <= TODAY(7), "Yellow", IF([Revised Due Date]@row >= TODAY(8), "Green"))), IF([Due Date]@row < TODAY(), "Red", IF([Due Date]@row <= TODAY(7), "Yellow", IF([Due Date]@row >= TODAY(8), "Green"))))

Essentially, if the 'Revised Due Date' is not blank, check it for the RAG criteria, else check the Due date for the RAG criteria.

All comments

Anupriya

You can try something on these lines -

Essentially, if the 'Revised Due Date' is not blank, check it for the RAG criteria, else check the Due date for the RAG criteria.

Carl Vieira

This works great, I really appreciate your help. Looking at the formula that makes total sense, I was trying an IF/OR and was getting nowhere

Thank you,

Carl

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.