Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

INVALID OPERATION error while building a IF/AND formula

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

  • 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!

Trending in Formulas and Functions