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")
Answers
-
Are all of the date columns right next to each other?
-
No, they are on the same row but separated by their associated task numbers
-
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")
-
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?
-
Oops. I didn't see part of your answer. I"ll try again
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!