How to write a function date function that doesn't activate when there are blanks

I am trying to write a function that has 3 different column dates that could be due and if any of them are due a string, "Renew" will be returned in a 4th column. It works great when all of the date fields are filled out, but also outputs "Renew" when any of the date columns are blank. How can I adjust this function so one or more of the date columns can be blank and not active the function?

=IF(OR(TODAY() > [IDF Task Date End]@row, TODAY() > [Escort Task End Date]@row, TODAY() > [Task End Date]@row), "Renew")

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Are all of the date columns right next to each other?

Lee133

No, they are on the same row but separated by their associated task numbers

Lee133

Paul Newcome

Try something like this:

[IDF Task Date End]@row, TODAY() > [Escort Task End Date]@row, TODAY() > [Task End Date]@row

=IF(MIN(IF([IDF Task Date End]@row <> "", [IDF Task Date End]@row, TODAY(1)), IF([Escort Task End Date]@row <> "", [Escort Task End Date]@row, TODAY(1)), IF([Task End Date]@row <> "", [Task End Date]@row, TODAY(1))) < TODAY(), "Renew")

Lee133

Hi. Thanks for helping me! I put in your formula which works if the date is either blank or has a date in the past, but give me a "#INVALID DATA TYPE error if I have a date in the future. Any ideas?

Lee133

Oops. I didn't see part of your answer. I"ll try again

Paul Newcome

Let me know how the retry goes, and please ignore the middle bit of my last response. I had copy/pasted that from your formula so I would have the column names handy without having to scroll and forgot to delete it.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.