Invalid Operation formula question
The following formula is pulling an #INVALID OPERATION error when a date is entered into the Date Received column - anyone have an idea of what the error is? The column date is set to Date. Thanks in advance!
=IF([Date Received]11 = 0, IF($[Q3 Estimated Completion Date]$8 > [Q3 Estimated Completion Date]11, "Late & Outstanding", IF($[Q3 Estimated Completion Date]$8 = [Q3 Estimated Completion Date]11, "Due Today Not Rec", "Not Yet Due")), IF([Date Received]11 < [Q3 Estimated Completion Date]11, "Early", IF([Q3 Estimated Completion Date]11 > ([Date Received]11 - 1), "On Time", "Rec Late")))
Comments
-
Hi Jess,
Is the [Q3 Estimated Completion Date] also a date formatted column?
The inclusion of IF([Date Received]11 < [Q3 Estimated Completion Date]11 and IF([Q3 Estimated Completion Date]11 > ([Date Received]11 - 1) will produce an error if you are comparing a date format to a text/number format.
The easiest way to debug is to copy each IF statement out separately and then see which one is producing the error.
-
Hi Chris,
Yes - the [Q3 Estimated Completion Date] column is also formatted as a date.
I think it may be the last string - IF([Date Received]11 < [Q3 Estimated Completion Date]11, "Early", IF([Q3 Estimated Completion Date]11 > ([Date Received]11 - 1), "On Time", "Rec Late"))) but not 100% sure and unsure how to fix. I have dates populated in the [Q3 Estimated Completion Date] and the formula will produce "Late & Outstanding", "Due Today Not Rec", or "Not Yet Due" when nothing is in the [Date Received] cell. That's when I get the Invalid Operation error.
-
Hi Jess,
I have created a mockup with the 2 date columns specified and I am not receiving any errors. That being said, the formula may not be calculating dates/statuses in the way you imagine.
Your formula is evaluating against the following criteria:
- IF [Date Received]11 is blank AND [Q3 Estimated Completion Date]$8 is greater than [Q3 Estimated Completion Date]11 then the status is "Late & Outstanding"
- IF [Q3 Estimated Completion Date]$8 is equal to [Q3 Estimated Completion Date]11 then the status is "Due Today Not Rec"
- IF [Date Received]11 is blank AND condition 2 is not true then the status is "Not Yet Due"
- IF [Date Received]11 is less than [Q3 Estimated Completion Date]11 then the status is "Early"
- IF [Q3 Estimated Completion Date]11 is greater than [Date Received]11 - 1 day then the status is "On Time"
- If all other conditions are false then the status is "Rec Late"
Is this what you were after?
-
Hi Chris,
Thanks again for your response - I think there may have been an error in my criteria. They should be:
- IF [Date Received]11 is blank AND [Q3 Estimated Completion Date]$8 is greater (after) than [Q3 Estimated Completion Date]11 then the status is "Late & Outstanding"
- IF [Q3 Estimated Completion Date]$8 is equal to [Q3 Estimated Completion Date]11 AND [Date Received]11 then the status is "Due Today Not Rec"
- IF [Date Received]11 is blank AND condition 2 is not true then the status is "Not Yet Due"
- IF [Date Received]11 is less (earlier) than [Q3 Estimated Completion Date]11 then the status is "Early"
- IF [Q3 Estimated Completion Date]11 is equal to [Date Received]11 then the status is "On Time"
- If [Date Received]11 is greater (after) [Q3 Estimated Completion Date]11 then the status is "Rec Late"
-
Hi Jess,
I don't think dates can be compared to numbers (including zero), even though it does allow you to compare a BLANK cell to zero. So you might change the first condition in the formula:
[Date Received]11 = 0
to:
ISBLANK([Date Received]11)
Hopefully that will work for blank cells and dated cells.
-
Hi Jess,
Diane is correct abut using ISBLANK instead of =0. It's best practice to use ISLANK regardless of whether the formula actually works.
Based on your criteria (which I'll have to trust is correct as I don't know the structure of your sheet), but this will work:
=IF(AND(ISBLANK([Date Received]11), [Q3 Estimated Completion Date]$8 > [Q3 Estimated Completion Date]11), "Late & Outstanding", IF(AND([Q3 Estimated Completion Date]$8 = [Q3 Estimated Completion Date]11, [Q3 Estimated Completion Date]$8 = [Date Received]11), "Due Today Not Rec", IF(AND(ISBLANK([Date Received]11), [Q3 Estimated Completion Date]$8 = [Q3 Estimated Completion Date]11, [Q3 Estimated Completion Date]$8 = [Date Received]11), "Not Yet Due", IF([Date Received]11 < [Q3 Estimated Completion Date]11, "Early", IF([Q3 Estimated Completion Date]11 = [Date Received]11, "On Time", IF([Date Received]11 > [Q3 Estimated Completion Date]11, "Rec Late", ""))))))
-
That worked! Thanks so much Diane and Chris!
-
Hi Jess,
Happy to hear that. Best of luck with your sheet .
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives