Formula based on date and status columns

I currently have the below formula in a column titled "Health' to create a symbol if the request was created more than 21 days ago (red) or less than 21 days ago (green). However, I want this formula to also include the status column that would include "received" or "open" and exclude the "submitted" status. How do I do this?

Current formula without status column being included:

=IF([Request Creation Date]@row < TODAY(-21), "Red", "Green"

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Good evening,

    Try:

    =IF(AND([Request Creation Date]@row< TODAY(-21), Status@row <> "submitted"), "Red", "Green")

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • I inserted the below formula, (note: the accurate column title is "Submitted (SOW presented to requester, waiting response)" and the rows that have been submitted are still returning a red or green symbol.

    =IF(AND([Request Creation Date]@row < TODAY(-21), Status@row <> "[Submitted (SOW presented to requester, waiting response)]"), "Red", "Green")

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Try:

    =IF(AND([Request Creation Date]@row < TODAY(-21), [Submitted (SOW presented to requester, waiting response)]@row <> "Submitted"), "Red", "Green")

    Work this time?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!