# RAG Looking at Two Dates

Options
✭✭✭
edited 01/19/22

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

• ✭✭✭✭
Options

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.

• ✭✭✭✭
Options

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.

• ✭✭✭
Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!