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 columntype the "Actual exfactory 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.
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
@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 exfactory date ACT samples]@row = "", [Actual exfactory date ACT samples]@row = ""), [ACT sample needed?]@row = "Yes"), "Yellow", IF(AND([Actual exfactory date ACT samples]@row < DATE(2022, 1, 22), [ACT sample needed?]@row = "Yes"), "Green", IF(AND([ACT sample needed?]@row = "", [Actual exfactory date ACT samples]@row = ""), "not needed", IF(AND([ACT sample needed?]@row = "Yes", [Actual exfactory date ACT samples]@row <> "", [Planned exfactory date ACT samples]@row <> "", [Actual exfactory date ACT samples]@row >= DATE(2022, 1, 22)), "Red"))))
And Yes [Actual exfactory date ACT samples]@row and [Planned exfactory date ACT samples]@row are formatted as DATE.

Would you mind doublechecking the [Actual exfactory date ACT 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):
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
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 exfactory date ACT samples]@row = "", [Actual exfactory date ACT samples]@row = ""), [ACT sample needed?]@row = "Yes"), "Yellow", IF(AND([Actual exfactory date ACT samples]@row <> "", [Actual exfactory date ACT samples]@row < DATE(2022, 1, 22), [ACT sample needed?]@row = "Yes"), "Green", IF(AND([ACT sample needed?]@row = "", [Actual exfactory date ACT samples]@row = ""), "not needed", IF(AND([ACT sample needed?]@row = "Yes", [Actual exfactory date ACT samples]@row <> "", [Planned exfactory date ACT samples]@row <> "", [Actual exfactory date ACT 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 exfactory date ACT samples]@row = "", [Actual exfactory date ACT samples]@row = ""), [ACT sample needed?]@row = "Yes"), "Yellow", IF(AND([Actual exfactory date ACT samples]@row = DATE, [Actual exfactory date ACT samples]@row < DATE(2022, 1, 22), [ACT sample needed?]@row = "Yes"), "Green", IF(AND([ACT sample needed?]@row = "", [Actual exfactory date ACT samples]@row = ""), "not needed", IF(AND([ACT sample needed?]@row = "Yes", [Actual exfactory date ACT samples]@row <> "", [Planned exfactory date ACT samples]@row <> "", [Actual exfactory date ACT 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 exfactory date ACT samples]@row = "", [Actual exfactory date ACT samples]@row = ""), [ACT sample needed?]@row = "Yes"), "Yellow", IF(AND(ISDATE([Actual exfactory date ACT samples]@row), [Actual exfactory date ACT samples]@row < DATE(2022, 1, 22), [ACT sample needed?]@row = "Yes"), "Green", IF(AND([ACT sample needed?]@row = "", [Actual exfactory date ACT samples]@row = ""), "not needed", IF(AND([ACT sample needed?]@row = "Yes", [Actual exfactory date ACT samples]@row <> "", [Planned exfactory date ACT samples]@row <> "", [Actual exfactory date ACT samples]@row >= DATE(2022, 1, 22)), "Red"))))
Help Article Resources
Categories
Check out the Formula Handbook template!