Map values to date buckets

Hi

I have the following table:

Sprint, Start date, End date

20.01, 10/01/20, 10/15/20

20.02, 10/15/20, 10/30/20

20.03, 11/01/20, 11/15/20


I want to show the Sprint value, whenever a user enters a date between the dates that are next to the name.

For example, show 20.01 when a user enters a date from 10/01 - 10/15.


How is this possible?

Best Answer

Answers

  • Hi @Genevieve P

    Thank you for your reply. My Sprints table is on a separate sheet so we are dealing with a cross-sheet reference.

    This is my formula:

    =INDEX(COLLECT({Sprint dates Range 1}, {Sprint dates Range 2}, <=[Start date]@row, {Sprint dates Range 3}, >=[End date]@row), 1)

    Where

    Sprint dates Range 1: All the Sprint names (20.01, 20.02...)

    Sprint dates Range 2: All the Start dates

    Sprint dates Range 3: All the End dates


    It works like a treat!!! :-)

    Thanks

    Agis

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi Agis,

    I'm glad you were able to make the INDEX(COLLECT work for you! Thanks for following up with your solution. 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!