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
Answers

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

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

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

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

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

Column type is A1 Text/number
Help Article Resources
Categories
Check out the Formula Handbook template!