Map values to date buckets

Agis Kalogiannis

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?

Genevieve P.

Hi @Agis Kalogiannis

You can do this with an INDEX(COLLECT, looking for two criteria (that the End Date is greater-than-or-equal-to this current date, or that the Start Date is less-than-or-equal-to the this current date).

This is the structure for an IndexCollect if it's a cross-sheet formula:

=INDEX(COLLECT({range to pull from}, {first criteria range}, first criteria, {second criteria range}, second criteria), 1)

In your instance, it sounds like this table is at the top of the sheet, is that correct? If so, you can have your ranges be locked in to look at certain rows. For example, if you just have 3 rows with this table, you can lock in the row numbers with an absolute reference or a $ sign: Sprint$1:Sprint$3

Try something like this:

=INDEX(COLLECT(Sprint$1:Sprint$3, [Start Date]$1:[Start Date]$3, <= [Date Input]@row, [End Date]$1:[End Date]$3, >= [Date Input]@row), 1)

The "Date Input" column is what I named whatever column your users will be putting the date into. Let me know if this works for you! If not, it would be helpful to see some screen captures and know a bit more about your set up.

Cheers,

Genevieve

Agis Kalogiannis

Anyone?

Genevieve P.

Hi @Agis Kalogiannis

You can do this with an INDEX(COLLECT, looking for two criteria (that the End Date is greater-than-or-equal-to this current date, or that the Start Date is less-than-or-equal-to the this current date).

This is the structure for an IndexCollect if it's a cross-sheet formula:

=INDEX(COLLECT({range to pull from}, {first criteria range}, first criteria, {second criteria range}, second criteria), 1)

In your instance, it sounds like this table is at the top of the sheet, is that correct? If so, you can have your ranges be locked in to look at certain rows. For example, if you just have 3 rows with this table, you can lock in the row numbers with an absolute reference or a $ sign: Sprint$1:Sprint$3

Try something like this:

=INDEX(COLLECT(Sprint$1:Sprint$3, [Start Date]$1:[Start Date]$3, <= [Date Input]@row, [End Date]$1:[End Date]$3, >= [Date Input]@row), 1)

The "Date Input" column is what I named whatever column your users will be putting the date into. Let me know if this works for you! If not, it would be helpful to see some screen captures and know a bit more about your set up.

Cheers,

Genevieve

Agis Kalogiannis

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.

Hi Agis,

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