What is wrong with my formula??

terrc046

The first part of the formula works because I made changes (putting in one date in [Panel Date]2) to the sheet to get one of the true_values in the IF function and it came back correct, but when I try to make the same changes farther down my sheet (adding a date to [Panel Date]6) that matches the rest of the formula, this error message pops up: "INVALID OPERATION."

Can anyone tell me what's wrong with this formula?

=IF(AND([Executive Panel]1 = 1, NOT(ISBLANK([Panel Date]2)), [Executive Panel]2 = 0, [Executive Panel]5 = 0, [Panel Date]6 = 0, [Executive Panel]6 = 0, [Executive Panel]9 = 0, [Panel Date]10 = 0, [Executive Panel]10 = 0, [Executive Panel]13 = 0, [Panel Date]14 = 0, [Executive Panel]14 = 0), "Scheduled", IF(AND([Executive Panel]1 = 1, NOT(ISBLANK([Panel Date]2)), [Executive Panel]2 = 1, [Executive Panel]5 = 1, NOT(ISBLANK([Panel Date]6)), [Executive Panel]6 = 0, [Executive Panel]9 = 0, [Panel Date]10 = 0, [Executive Panel]10 = 0, [Executive Panel]13 = 0, [Panel Date]14 = 0, [Executive Panel]14 = 0), "Scheduled", IF(AND([Executive Panel]1 = 1, NOT(ISBLANK([Panel Date]2)), [Executive Panel]2 = 1, [Executive Panel]5 = 1, NOT(ISBLANK([Panel Date]6)), [Executive Panel]6 = 1, [Executive Panel]9 = 1, NOT(ISBLANK([Panel Date]10)), [Executive Panel]10 = 0, [Executive Panel]13 = 0, [Panel Date]14 = 0, [Executive Panel]14 = 0), "Scheduled", IF(AND([Executive Panel]1 = 1, NOT(ISBLANK([Panel Date]2)), [Executive Panel]2 = 1, [Executive Panel]5 = 1, NOT(ISBLANK([Panel Date]6)), [Executive Panel]6 = 1, [Executive Panel]9 = 1, NOT(ISBLANK([Panel Date]10)), [Executive Panel]10 = 1, [Executive Panel]13 = 1, NOT(ISBLANK([Panel Date]14)), [Executive Panel]14 = 0), "Scheduled", IF(AND([Executive Panel]1 = 1, NOT(ISBLANK([Panel Date]2)), [Executive Panel]2 = 1, [Executive Panel]5 = 0, [Panel Date]6 = 0, [Executive Panel]6 = 0, [Executive Panel]9 = 0, [Panel Date]10 = 0, [Executive Panel]10 = 0, [Executive Panel]13 = 0, [Panel Date]14 = 0, [Executive Panel]14 = 0), "Completed", IF(AND([Executive Panel]1 = 1, NOT(ISBLANK([Panel Date]2)), [Executive Panel]2 = 1, [Executive Panel]5 = 1, NOT(ISBLANK([Panel Date]6)), [Executive Panel]6 = 1, [Executive Panel]9 = 0, [Panel Date]10 = 0, [Executive Panel]10 = 0, [Executive Panel]13 = 0, [Panel Date]14 = 0, [Executive Panel]14 = 0), "Completed", IF(AND([Executive Panel]1 = 1, NOT(ISBLANK([Panel Date]2)), [Executive Panel]2 = 1, [Executive Panel]5 = 1, NOT(ISBLANK([Panel Date]6)), [Executive Panel]6 = 1, [Executive Panel]9 = 1, NOT(ISBLANK([Panel Date]10)), [Executive Panel]10 = 1, [Executive Panel]13 = 0, [Panel Date]14 = 0, [Executive Panel]14 = 0), "Completed", IF(AND([Executive Panel]1 = 1, NOT(ISBLANK([Panel Date]2)), [Executive Panel]2 = 1, [Executive Panel]5 = 1, NOT(ISBLANK([Panel Date]6)), [Executive Panel]6 = 1, [Executive Panel]9 = 1, NOT(ISBLANK([Panel Date]10)), [Executive Panel]10 = 1, [Executive Panel]13 = 1, NOT(ISBLANK([Panel Date]14)), [Executive Panel]14 = 1), "Completed", "Not Started"))))))))

terrc046

Hi @Kelly Moore ,

Thank you for responding! I didn't know about ISDATE, so that is helpful in shortening my formula! My goal with ISDATE was to get a certain result when there is any date in a cell in the [Panel Date] column and another result when there is no date in the cell. So I don't always want the cells in the [Panel Date] column to have an ISDATE function; sometimes I'd need them to = 0/be blank. But adding the ISDATE and doing ISBLANK instead of = 0 for the [Panel Date] cells that I want to be blank worked, so thank you for your help! The corrected formula is below. (I also don't need any more rows on this sheet, thankfully, so I won't need to lengthen the formula in the future.)

=IF(AND([Executive Panel]1 = 1, ISDATE([Panel Date]2), [Executive Panel]2 = 0, [Executive Panel]5 = 0, ISBLANK([Panel Date]6), [Executive Panel]6 = 0, [Executive Panel]9 = 0, ISBLANK([Panel Date]10), [Executive Panel]10 = 0, [Executive Panel]13 = 0, ISBLANK([Panel Date]14), [Executive Panel]14 = 0), "Scheduled", IF(AND([Executive Panel]1 = 1, ISDATE([Panel Date]2), [Executive Panel]2 = 1, [Executive Panel]5 = 1, ISDATE([Panel Date]6), [Executive Panel]6 = 0, [Executive Panel]9 = 0, ISBLANK([Panel Date]10), [Executive Panel]10 = 0, [Executive Panel]13 = 0, ISBLANK([Panel Date]14), [Executive Panel]14 = 0), "Scheduled", IF(AND([Executive Panel]1 = 1, ISDATE([Panel Date]2), [Executive Panel]2 = 1, [Executive Panel]5 = 1, ISDATE([Panel Date]6), [Executive Panel]6 = 1, [Executive Panel]9 = 1, ISDATE([Panel Date]10), [Executive Panel]10 = 0, [Executive Panel]13 = 0, ISBLANK([Panel Date]14), [Executive Panel]14 = 0), "Scheduled", IF(AND([Executive Panel]1 = 1, ISDATE([Panel Date]2), [Executive Panel]2 = 1, [Executive Panel]5 = 1, ISDATE([Panel Date]6), [Executive Panel]6 = 1, [Executive Panel]9 = 1, ISDATE([Panel Date]10), [Executive Panel]10 = 1, [Executive Panel]13 = 1, ISDATE([Panel Date]14), [Executive Panel]14 = 0), "Scheduled", IF(AND([Executive Panel]1 = 1, ISDATE([Panel Date]2), [Executive Panel]2 = 1, [Executive Panel]5 = 0, ISBLANK([Panel Date]6), [Executive Panel]6 = 0, [Executive Panel]9 = 0, ISBLANK([Panel Date]10), [Executive Panel]10 = 0, [Executive Panel]13 = 0, ISBLANK([Panel Date]14), [Executive Panel]14 = 0), "Completed", IF(AND([Executive Panel]1 = 1, ISDATE([Panel Date]2), [Executive Panel]2 = 1, [Executive Panel]5 = 1, ISDATE([Panel Date]6), [Executive Panel]6 = 1, [Executive Panel]9 = 0, ISBLANK([Panel Date]10), [Executive Panel]10 = 0, [Executive Panel]13 = 0, ISBLANK([Panel Date]14), [Executive Panel]14 = 0), "Completed", IF(AND([Executive Panel]1 = 1, ISDATE([Panel Date]2), [Executive Panel]2 = 1, [Executive Panel]5 = 1, ISDATE([Panel Date]6), [Executive Panel]6 = 1, [Executive Panel]9 = 1, ISDATE([Panel Date]10), [Executive Panel]10 = 1, [Executive Panel]13 = 0, ISBLANK([Panel Date]14), [Executive Panel]14 = 0), "Completed", IF(AND([Executive Panel]1 = 1, ISDATE([Panel Date]2), [Executive Panel]2 = 1, [Executive Panel]5 = 1, ISDATE([Panel Date]6), [Executive Panel]6 = 1, [Executive Panel]9 = 1, ISDATE([Panel Date]10), [Executive Panel]10 = 1, [Executive Panel]13 = 1, ISDATE([Panel Date]14), [Executive Panel]14 = 1), "Completed", "Not Started"))))))))

Kelly Moore

Hey @terrc046

I can replicate your Invalid Operation error. When I changed your date criteria from = 0 (for example, [Panel Date]6=0) to ISDATE(Panel Date]6), the error clears. Is your date column formatted as a smartsheet date column? If it is a formatted date column, what is actually displayed in the date cells when you expect the cell to be equal to zero? Is it possible to show a screenshot of your data, at least rows 1-14. There might be an opportunity to simplify your formula. If you sheet grows, this formula will be extremely difficult to maintain.

Here's the start of removing the error from your formula. Not knowing what the zeros were helping you do, replacing your =0 with the ISDATEs may not be what you need. I also changed your NOT(ISBLANK()) to ISDATEs - which also might not be what you need. An alternative to NOT(ISBLANK()) is <>"" . The <>"" means the same thing and is sometimes more straightforward than keeping up with the additional parentheses as well as saving characters if one starts approaching the max character count in a cell (4000 characters is limit)

=IF(AND([Executive Panel]1 = 1, ISDATE([Panel Date]2), [Executive Panel]2 = 0, [Executive Panel]5 = 0, ISDATE([Panel Date]6), [Executive Panel]6 = 0, [Executive Panel]9 = 0, ISDATE([Panel Date]10), [Executive Panel]10 = 0, [Executive Panel]13 = 0, ISDATE([Panel Date]14), [Executive Panel]14 = 0), "Scheduled", IF(AND([Executive Panel]1 = 1, ISDATE([Panel Date]2), [Executive Panel]2 = 1, [Executive Panel]5 = 1, ISDATE([Panel Date]6), [Executive Panel]6 = 0, [Executive Panel]9 = 0, ISDATE([Panel Date]10), [Executive Panel]10 = 0, [Executive Panel]13 = 0, ISDATE([Panel Date]14), [Executive Panel]14 = 0), "Scheduled", IF(AND([Executive Panel]1 = 1, ISDATE([Panel Date]2), [Executive Panel]2 = 1, [Executive Panel]5 = 1, ISDATE([Panel Date]6), [Executive Panel]6 = 1, [Executive Panel]9 = 1, ISDATE([Panel Date]10), [Executive Panel]10 = 0, [Executive Panel]13 = 0, ISDATE([Panel Date]14), [Executive Panel]14 = 0), "Scheduled", IF(AND([Executive Panel]1 = 1, ISDATE([Panel Date]2), [Executive Panel]2 = 1, [Executive Panel]5 = 1, ISDATE([Panel Date]6), [Executive Panel]6 = 1, [Executive Panel]9 = 1, ISDATE([Panel Date]10), [Executive Panel]10 = 1, [Executive Panel]13 = 1, ISDATE([Panel Date]14), [Executive Panel]14 = 0), "Scheduled", IF(AND([Executive Panel]1 = 1, ISDATE([Panel Date]2), [Executive Panel]2 = 1, [Executive Panel]5 = 0, ISDATE([Panel Date]6), [Executive Panel]6 = 0, [Executive Panel]9 = 0, ISDATE([Panel Date]10), [Executive Panel]10 = 0, [Executive Panel]13 = 0, ISDATE([Panel Date]14), [Executive Panel]14 = 0), "Completed", IF(AND([Executive Panel]1 = 1, ISDATE([Panel Date]2), [Executive Panel]2 = 1, [Executive Panel]5 = 1, ISDATE([Panel Date]6), [Executive Panel]6 = 1, [Executive Panel]9 = 0, ISDATE([Panel Date]10), [Executive Panel]10 = 0, [Executive Panel]13 = 0, ISDATE([Panel Date]14), [Executive Panel]14 = 0), "Completed", IF(AND([Executive Panel]1 = 1, ISDATE([Panel Date]2), [Executive Panel]2 = 1, [Executive Panel]5 = 1, ISDATE([Panel Date]6), [Executive Panel]6 = 1, [Executive Panel]9 = 1, ISDATE([Panel Date]10), [Executive Panel]10 = 1, [Executive Panel]13 = 0, ISDATE([Panel Date]14), [Executive Panel]14 = 0), "Completed", IF(AND([Executive Panel]1 = 1, ISDATE([Panel Date]2), [Executive Panel]2 = 1, [Executive Panel]5 = 1, ISDATE([Panel Date]6), [Executive Panel]6 = 1, [Executive Panel]9 = 1, ISDATE([Panel Date]10), [Executive Panel]10 = 1, [Executive Panel]13 = 1, ISDATE([Panel Date]14), [Executive Panel]14 = 1), "Completed", "Not Started"))))))))

Does this work for you?

Kelly

terrc046

Hi @Kelly Moore ,

Thank you for responding! I didn't know about ISDATE, so that is helpful in shortening my formula! My goal with ISDATE was to get a certain result when there is any date in a cell in the [Panel Date] column and another result when there is no date in the cell. So I don't always want the cells in the [Panel Date] column to have an ISDATE function; sometimes I'd need them to = 0/be blank. But adding the ISDATE and doing ISBLANK instead of = 0 for the [Panel Date] cells that I want to be blank worked, so thank you for your help! The corrected formula is below. (I also don't need any more rows on this sheet, thankfully, so I won't need to lengthen the formula in the future.)

=IF(AND([Executive Panel]1 = 1, ISDATE([Panel Date]2), [Executive Panel]2 = 0, [Executive Panel]5 = 0, ISBLANK([Panel Date]6), [Executive Panel]6 = 0, [Executive Panel]9 = 0, ISBLANK([Panel Date]10), [Executive Panel]10 = 0, [Executive Panel]13 = 0, ISBLANK([Panel Date]14), [Executive Panel]14 = 0), "Scheduled", IF(AND([Executive Panel]1 = 1, ISDATE([Panel Date]2), [Executive Panel]2 = 1, [Executive Panel]5 = 1, ISDATE([Panel Date]6), [Executive Panel]6 = 0, [Executive Panel]9 = 0, ISBLANK([Panel Date]10), [Executive Panel]10 = 0, [Executive Panel]13 = 0, ISBLANK([Panel Date]14), [Executive Panel]14 = 0), "Scheduled", IF(AND([Executive Panel]1 = 1, ISDATE([Panel Date]2), [Executive Panel]2 = 1, [Executive Panel]5 = 1, ISDATE([Panel Date]6), [Executive Panel]6 = 1, [Executive Panel]9 = 1, ISDATE([Panel Date]10), [Executive Panel]10 = 0, [Executive Panel]13 = 0, ISBLANK([Panel Date]14), [Executive Panel]14 = 0), "Scheduled", IF(AND([Executive Panel]1 = 1, ISDATE([Panel Date]2), [Executive Panel]2 = 1, [Executive Panel]5 = 1, ISDATE([Panel Date]6), [Executive Panel]6 = 1, [Executive Panel]9 = 1, ISDATE([Panel Date]10), [Executive Panel]10 = 1, [Executive Panel]13 = 1, ISDATE([Panel Date]14), [Executive Panel]14 = 0), "Scheduled", IF(AND([Executive Panel]1 = 1, ISDATE([Panel Date]2), [Executive Panel]2 = 1, [Executive Panel]5 = 0, ISBLANK([Panel Date]6), [Executive Panel]6 = 0, [Executive Panel]9 = 0, ISBLANK([Panel Date]10), [Executive Panel]10 = 0, [Executive Panel]13 = 0, ISBLANK([Panel Date]14), [Executive Panel]14 = 0), "Completed", IF(AND([Executive Panel]1 = 1, ISDATE([Panel Date]2), [Executive Panel]2 = 1, [Executive Panel]5 = 1, ISDATE([Panel Date]6), [Executive Panel]6 = 1, [Executive Panel]9 = 0, ISBLANK([Panel Date]10), [Executive Panel]10 = 0, [Executive Panel]13 = 0, ISBLANK([Panel Date]14), [Executive Panel]14 = 0), "Completed", IF(AND([Executive Panel]1 = 1, ISDATE([Panel Date]2), [Executive Panel]2 = 1, [Executive Panel]5 = 1, ISDATE([Panel Date]6), [Executive Panel]6 = 1, [Executive Panel]9 = 1, ISDATE([Panel Date]10), [Executive Panel]10 = 1, [Executive Panel]13 = 0, ISBLANK([Panel Date]14), [Executive Panel]14 = 0), "Completed", IF(AND([Executive Panel]1 = 1, ISDATE([Panel Date]2), [Executive Panel]2 = 1, [Executive Panel]5 = 1, ISDATE([Panel Date]6), [Executive Panel]6 = 1, [Executive Panel]9 = 1, ISDATE([Panel Date]10), [Executive Panel]10 = 1, [Executive Panel]13 = 1, ISDATE([Panel Date]14), [Executive Panel]14 = 1), "Completed", "Not Started"))))))))