Return Sprint based on finish date

I have a project schedule where I am tracking activities. I would like to return a sprint number in column "Sprint" based on the finish date. Example: Sprint 1 is 6/23 - 7/6 and the task ends 6/29 so I would like the column to return a value of "Q1 Sprint 1" I have set up a separate sheet with the Sprints and start/finish dates but not sure if that is the best method. Any recommendations on how to build this formula?

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Hi Alethea,

    Can you attach a screen shot of your sheet and an explanation of how you determine the naming - "Q1 Sprint 1" based on dates?

    Happy to help.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Hi Mark,

    Thank you for reaching out. I've attached two screenshots - one of my project schedule and one of the sprint schedule I created in a separate sheet. I would like the sprint to auto-populate in the project schedule based on the end date.


  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Hi Alethea,

    Looks like you can do this with a VLOOKUP if you move your Sprint sheet Finish column to the far left. Or, duplicate the sprint column to the right of Finish.

    I'm you project sheet use this formula in the Sprint column:

    =VLOOKUP(Finish@row, {insert external range}, 2)

    Your external range will be your Sprint sheet with Finish as the first left column and Sprint as the 2nd column.

    VLOOKUP will find a match or the first number below the lookup value. Should work for you.

    Help?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!