Formula to return last completed phase

Brian W

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?

Brian W

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.