if and blank

Sophie Humphrey
Sophie Humphrey ✭✭✭✭
edited 01/13/21 in Formulas and Functions

Hi,


I have a date that a survey was completed and a date that someone has contacted a customer and I'm looking to create a formula that highlights if the response date was over 3 days ago AND there has not been a successful call (the contact date column will be blank) then mark this as red, if the response data was over 3 days ago and there has been a successful call (the contact date column will have a date) mark it as green.


I have tried:

=IF(AND([Survey Response Date]@row >TODAY(+3) , [Successful Contact Date]@row = ""), "Red", "Green")

Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You are very close. Try switching your date argument up a little...

    =IF(AND([Survey Response Date]@row < TODAY(-3), [Successful Contact Date]@row = ""), "Red", "Green")


    It helps me to think of dates as numbers with TODAY() being zero. Yesterday would be -1. The day before would be -2. So on and so forth. So more than 3 days in the past would be "less than today minus three".

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!