invalid operation

Christine Menke

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

Paul Newcome

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?

Genevieve P.

Hi @Christine Menke

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.

Christine Menke

@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.

Genevieve P.

Hi @Christine Menke

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):

Christine Menke

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?

Paul Newcome

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?

Christine Menke

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"))))

Paul Newcome

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"))))