if and blank

Sophie Humphrey

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

Paul Newcome

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

Bassam Khalil

Hi @Sophie Humphrey

Hope you are fine, please add a column to check How many days have passed since the Survey Response Date use this formula to calculate

=TODAY() - [Survey Response Date]@row

then ad conditional formating as in the following screenshot

Sophie Humphrey

Hi,

I am well, thank you. I hope you are too.

Thank you for your help, that's working perfectly!

Thanks,

Sophie

Paul Newcome

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

Sophie Humphrey

Thanks @Paul Newcome! I has suspected that was the issue but couldn't quite get it there. Thank you for your helpful explanation!

Paul Newcome

Happy to help. 👍️