If/Then to have date field modify status field.

AMCP

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

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.

=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, [Start Date]@row < [Actual End]@row), "COMPLETE", "NOT STARTED"))

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