using priority with Today function

I am trying to use priority symbols for two separate date columns to report "high for 60 days out, "Medium for 180 days out, and "Low" for everything else based off the two columns: Landlord Notice Date and Expiration Date, any help would be appreciated!

=IF([Landlord Notice Date]@row <= TODAY(60), "High", IF([Expiration Date (Current)]@row <= TODAY(60), "High", IF(AND([Landlord Notice Date]@row <= TODAY(180), [Landlord Notice Date]@row > TODAY(60), "Medium", IF(AND([Expiration Date (Current)]@row > TODAY(60), [Expiration Date (Current)]@row <= TODAY(180), IF([Landlord Notice Date]@row > TODAY(180), "Low", IF([Expiration Date (Current)]@row > TODAY(180), "Medium", "Low"))))))))

Answers

  • Devin Lee
    Devin Lee ✭✭✭✭✭

    Hey @Kristen Roberson

    Try this out.

    =IF(OR([Landlord Notice Date]@row <= TODAY(60), [Expiration Date (Current)]@row <= TODAY(60)), "High", IF(OR([Landlord Notice Date]@row <= TODAY(180), [Expiration Date (Current)]@row <= TODAY(180)), "Medium", "Low"))

    If you want to use symbols you can which the column properties to symbols and select the red, yellow, green option and use the formula below

    =IF(OR([Landlord Notice Date]@row <= TODAY(60), [Expiration Date (Current)]@row <= TODAY(60)), "Red", IF(OR([Landlord Notice Date]@row <= TODAY(180), [Expiration Date (Current)]@row <= TODAY(180)), "Yellow", "Green"))


  • @Devin Lee Devin, thank you for the input something is still off, its returning primarily High priority even with Blank date fields , which I think may be the issue.


    =IF(OR([Landlord Notice Date]@row <= TODAY(60), [Expiration Date (Current)]@row <= TODAY(60)), "High", IF(OR([Landlord Notice Date]@row <= TODAY(180), [Expiration Date (Current)]@row <= TODAY(180)), "Medium", "Low"))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!