RAG Looking at Two Dates

Carl Vieira

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

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.

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.

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