Blank cells causing INVALID OPERATION error

Hi -
I hope someone can tell me what I need to add to make this work. I have three columns: Due Date, Date Received, and Milestone Exceeded (Flag column). The goal is to have the flag turn red if 1)the due date has passed and the date received is blank, and when 2)the date received is past the due date. The formula I created for the flag column works for those two things. The problem comes when the Due Date is blank (Due date is generated by a formula when previous cell gets filled in). When Due Date is blank, I get #INVALID OPERATION error. I would like the flag to remain blank when the Due Date is blank. Here is what I have so far:
=IF(OR(AND(ISBLANK([Date Received]@row), TODAY() > [Due Date]@row), 1), IF([Date Received]@row > [Due Date]@row, 1))
What do I need to add to avoid the error message when the Due Date is blank?
Thanks for your assistance!
Answers
-
Try an IFERROR like so:
=IFERROR(IF(OR(AND(ISBLANK([Date Received]@row), TODAY() > [Due Date]@row), 1), IF([Date Received]@row > [Due Date]@row, 1)), "")
-
It worked! Thanks so much, Paul! (Wish I had reached out sooner!) I had tried the IFERROR but had the syntax wrong.
Best,
J
-
Sorry, I spoke too soon. It worked for the INVALID OPERATION error, but now the flag is not red when the Due Date has passed and the Date Received is empty?? Thoughts?
-
It looks like there is a syntax issue with the main part of the formula. Does this work?
=IFERROR(IF(OR(AND([Date Received]@row = "", TODAY() > [Due Date]@row), [Date Received]@row > [Due Date]@row), 1), "")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!