RAG Looking at Two Dates

Carl Vieira
Carl Vieira ✭✭✭
edited 01/19/22 in Formulas and Functions

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

Best Answer

  • Anupriya
    Anupriya ✭✭✭✭
    Answer ✓

    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.

Answers

  • Anupriya
    Anupriya ✭✭✭✭
    Answer ✓

    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
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!