if and blank

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

Find more posts tagged with

Formulas

Accepted answers

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

All comments

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

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. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.