invalid operation
Hi Experts,
I got the error invalid operation, but I could not find the mistake in my formula.
Would be great if somebody could help?
Thanks
Answers
-
I don't see anything right off. Are you able to copy/paste the formula directly from the sheet to hear so we can spread it out?
Is that the same formula that is generating the Yellows above?
-
To add to Paul's questions, can you confirm what column-type the "Actual ex-factory date" column is set to?
Since you're looking for < DATE(2022, 01, 22) in your second IF statement, if that column is set to Text/Number you would get an error.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Paul Newcome , @Genevieve P. thanks for your support!
Yes the formula is a column formula and works well for the yellow dots. I use the exact same formula on another sheet and it works there without this error.
Here is the formula
=IF(AND(OR([Planned ex-factory date AC-T samples]@row = "", [Actual ex-factory date AC-T samples]@row = ""), [AC-T sample needed?]@row = "Yes"), "Yellow", IF(AND([Actual ex-factory date AC-T samples]@row < DATE(2022, 1, 22), [AC-T sample needed?]@row = "Yes"), "Green", IF(AND([AC-T sample needed?]@row = "", [Actual ex-factory date AC-T samples]@row = ""), "not needed", IF(AND([AC-T sample needed?]@row = "Yes", [Actual ex-factory date AC-T samples]@row <> "", [Planned ex-factory date AC-T samples]@row <> "", [Actual ex-factory date AC-T samples]@row >= DATE(2022, 1, 22)), "Red"))))
And Yes [Actual ex-factory date AC-T samples]@row and [Planned ex-factory date AC-T samples]@row are formatted as DATE.
-
Would you mind double-checking the [Actual ex-factory date AC-T samples] column one more time?
Your formula works fine for me as long as the Column Properties for each of the three columns is set correctly:
But as soon as I change the Actual column to be a Text/Number column, I see the exact same error that you are seeing (once it hits the second IF statement):
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thanks @Genevieve P. ,
I found the reason why it's not working.
The Actual column is set as DATE.
But I have this formula in the Actual column to get the Date from another sheet:
=IFERROR(INDEX(COLLECT({awb report Testing Range 2}, {Copy of awb report Range 5}, @cell = [Article#]@row, {Copy of awb report Range 1}, @cell = "CR2"), 1), "")
as soon as I delete the formula it's working.
How could I change my formula that it's working now. I guess actual column is not " ", if I use the collect formula here?
-
I almost wonder if we couldn't just through in another "logical statement" in the second IF/AND...
=IF(AND(OR([Planned ex-factory date AC-T samples]@row = "", [Actual ex-factory date AC-T samples]@row = ""), [AC-T sample needed?]@row = "Yes"), "Yellow", IF(AND([Actual ex-factory date AC-T samples]@row <> "", [Actual ex-factory date AC-T samples]@row < DATE(2022, 1, 22), [AC-T sample needed?]@row = "Yes"), "Green", IF(AND([AC-T sample needed?]@row = "", [Actual ex-factory date AC-T samples]@row = ""), "not needed", IF(AND([AC-T sample needed?]@row = "Yes", [Actual ex-factory date AC-T samples]@row <> "", [Planned ex-factory date AC-T samples]@row <> "", [Actual ex-factory date AC-T samples]@row >= DATE(2022, 1, 22)), "Red"))))
It seems like it is throwing the error when it gets to that IF because the Actual date is blank.
Another option would be to maybe move the "Green" IF to after the "not needed" IF?
-
Hi @Paul Newcome ,
you are right <> "" cause the issue.
I tried to replace it with if it's a Date, but I guess the formula is not correct. Do you know what I need to write that this would work?
=IF(AND(OR([Planned ex-factory date AC-T samples]@row = "", [Actual ex-factory date AC-T samples]@row = ""), [AC-T sample needed?]@row = "Yes"), "Yellow", IF(AND([Actual ex-factory date AC-T samples]@row = DATE, [Actual ex-factory date AC-T samples]@row < DATE(2022, 1, 22), [AC-T sample needed?]@row = "Yes"), "Green", IF(AND([AC-T sample needed?]@row = "", [Actual ex-factory date AC-T samples]@row = ""), "not needed", IF(AND([AC-T sample needed?]@row = "Yes", [Actual ex-factory date AC-T samples]@row <> "", [Planned ex-factory date AC-T samples]@row <> "", [Actual ex-factory date AC-T samples]@row >= DATE(2022, 1, 22)), "Red"))))
-
This should work. You should also be able to restructure the order of your IF statements as well.
=IF(AND(OR([Planned ex-factory date AC-T samples]@row = "", [Actual ex-factory date AC-T samples]@row = ""), [AC-T sample needed?]@row = "Yes"), "Yellow", IF(AND(ISDATE([Actual ex-factory date AC-T samples]@row), [Actual ex-factory date AC-T samples]@row < DATE(2022, 1, 22), [AC-T sample needed?]@row = "Yes"), "Green", IF(AND([AC-T sample needed?]@row = "", [Actual ex-factory date AC-T samples]@row = ""), "not needed", IF(AND([AC-T sample needed?]@row = "Yes", [Actual ex-factory date AC-T samples]@row <> "", [Planned ex-factory date AC-T samples]@row <> "", [Actual ex-factory date AC-T samples]@row >= DATE(2022, 1, 22)), "Red"))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!