INVALID OPERATION error while building a IF/AND formula

Katy H
Katy H Community Champion

I am trying to build a formula that identifies project stages based on where we are date-wise in the project. I started building an embedded IF/AND statement that was looking at each project stage date range and if TODAY's date was in that range it would identify that as the stage. I got an INVALID OPERATION error so scaled down to a single IF statement to check and still got the same error.

I don't see any obvious issues with syntax but would love another pair of eyes.

The formula is reading two date columns and the output column is a dropdown single select.

Here is the simple IF formula: =IF(AND(TODAY() < [Flower End Date]@row, TODAY() >= [Flower Start Date]@row), "Flower", "")

Katy Hall

Head of Product Management

ILLA Canna

LinkedIn

Tags:

Best Answer

  • Katy H
    Katy H Community Champion
    Answer โœ“

    I think I was able to fix it, not sure about the diagnosis. I made a copy of the template set and made sure "Restrict to Date Only" was checked in ALL relevant date columns before adding dates. Then I wrote out the full formula, saved it, and then populated the date columns in the project template and the formula was spitting out the correct date.

    @Paul Newcome Thank you for your suggestion to evaluate date vs text. Still not sure why the output would be text in a project template... but as long as the problem is fixed, I suppose that is good enough.

    Wishing I had control center right about now, this update will take hours. :(

    Katy Hall

    Head of Product Management

    ILLA Canna

    LinkedIn

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!