Formula to return last completed phase
Hello Smartsheet Community.
I have created a formula that returns the current phase of a project based on the input of the project number:
=IFERROR(INDEX(COLLECT(Team:Team, Number:Number, NumberCheck@row, Parent:Parent, ISBLANK(@cell), Start:Start, @cell <= TODAY(), End:End, @cell >= TODAY()), 1), “")
It works great, but it returns a blank if the project is in lag time between phases. I can also get it to return either the current phase or the next upcoming phase if I look for the next end date that is in the future:
=IFERROR(INDEX(COLLECT(Team:Team, Number:Number, NumberCheck@row, Parent:Parent, ISBLANK(@cell), End:End, @cell >= TODAY()), 1), “”)
Is there a way to get it to return either the current phase or the last completed phase if the project is in lag time?
Comments
-
Well, I came up with a solution. Let me know if you think of something better.
I created a helper column that checks if the current phase has started, and if so it checks if the next phase is in the future.
=IF(Parent1 = "", IF(Start1 <= TODAY(), IF(Start1 >= TODAY(), 1, IF(Start2 > TODAY(), 1, 0))))
Then I adjusted my formula to pull from the row with the Current Phase checked:
=IFERROR(INDEX(COLLECT(Team:Team, Number:Number, NumberCheck@row, [Current Phase]:[Current Phase], @cell = 1), 1), “")
I may have unnecessary code in there, but it works and my brain hurts now.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 283 Events
- 33 Webinars
- 7.3K Forum Archives