Formula to return last completed phase

Options
Brian W
Brian W ✭✭
edited 12/09/19 in Smartsheet Basics

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?

Screen Shot 2018-12-13 at 10.28.23 AM.png

Comments

  • Brian W
    Brian W ✭✭
    Options

    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.