INVALID OPERATION error while building a IF/AND formula

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

Find more posts tagged with

Formulas

Accepted answers

Katy H

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. :(

All comments

Amanda Alv

Hi,

That exact formula worked for me; no changes were necessary. Did you double check that your column types are set as Date columns?

Katy H

I just triple-checked, and yes. They are both date columns. I am stumped!

Paul Newcome

How is the [Flower End Date] column being populated exactly?

Katy H

@Paul Newcome both date columns are fed by cell links.

Paul Newcome

Ok. Let's follow those back to the source then. How is the original source data populated, and are those source columns both set as date type columns?

A date type column can house text values, and even though it looks like a date, it may be stored as a text value depending on a number of variables.

Katy H

Paul,

I appreciate the help diagnosing!

Both of the cells are being fed by date columns in a sheet with project settings turned on.

Here are both of the columns the dates are recorded in. I also double-clicked on the date entries to confirm that they are at least superficially being fed as dates.

Screen Shot 2022-03-30 at 10.00.46 AM.png

Screen Shot 2022-03-30 at 10.00.37 AM.png

I also tried checking "restrict to dates only" and reset the dates in the sheet but the error persisted.

Here is a screenshot of one of the date values in the project sheet feeding one of the cell links.

Screen Shot 2022-03-30 at 10.01.48 AM.png

The simplified formula that is giving the INVALID OPERATION ERROR.

Screen Shot 2022-03-30 at 10.02.44 AM.png

Katy H

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

Paul Newcome

Glad you were able to get it sorted. 👍️

It may have been just a little bug in the sheet. My next suggestion was going to basically be turning it off then turning it back on again.

Remove formula completely.

Sign out.

Clear browser's cookies and cache.

Retype formula.

If that still didn't work it was going to be starting a new sheet over from scratch which is usually the least desirable of options.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.