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
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!