Looking to input a formula so that changing the "start date" of a row will change the "status" in the column next to it. Tried the following: =IF(AND([Start Date]@row [Start Date]@row, "In Progress", IF([Actual End]@row > [Start Date]@row, "Complete", "Not Started"))) Status overall: Not Started In Progress Blocked Complete I keep getting #INVALID OPERATION Thanks

If/Then to have date field modify status field.

Looking to input a formula so that changing the "start date" of a row will change the "status" in the column next to it.

Tried the following:

=IF(AND([Start Date]@row < [Est. End]@row, [Est. End]@row < [Actual End]@row, [Actual End]@row = ""), "IN PROGRESS", IF(AND([Start Date]@row < [Est. End]@row, [Est. End]@row < [Actual End]@row, <[Actual End]@row), "COMPLETE", "NOT STARTED"))

Formula that I worked on trying to get it to work.

=IF([Start Date]@row < [Est. End]@row, "Started",

IF([Est. End]@row > [Start Date]@row, "In Progress",

IF([Actual End]@row > [Start Date]@row, "Complete", "Not Started")))

Status overall:

Not Started

In Progress

Blocked

Complete

I keep getting #INVALID OPERATION

Thanks

Find more posts tagged with

Accepted answers

All comments

John_Foster

Hi @AMCP,

I have copied your formula that you had been working on and this seems to be working for me.

I have looked at the original formula you posted and the issue ap[pears to be with the section directly before the following section from it.

<[Actual End]@row), "COMPLETE", "NOT STARTED"))

It is unable to compare the Actual End as there is a comparison missing.

I hope this helps you.

John

AMCP

https://community.smartsheet.com/discussion/comment/414492#Comment_414492

Hi John,

Thank you so much for the reply, so does this mean I need to add something to the equation in order for it to work? Still somewhat confused on how to solve it

Thanks

John_Foster

https://community.smartsheet.com/discussion/comment/414494#Comment_414494

If you changed your original formula to the below which I believe is correct it should work.

This was the comparison I added to set the Complete status - [Start Date]@row < [Actual End]@row

John

AMCP

https://community.smartsheet.com/discussion/comment/414496#Comment_414496

Hi John,

I see what you mean now, however on my end when I input the new formula it has gone from #INVALID OPERATION to #INVALID DATA TYPE

Progress in a way, but I still seem to have issues with it

John_Foster

https://community.smartsheet.com/discussion/comment/414501#Comment_414501

Hi @AMCP

If you right click on the column where the formula is what is the column type?

AMCP

Column type is A1 Text/number

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.