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
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Anyone?
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
-
Hi Agis,
I'm glad you were able to make the INDEX(COLLECT work for you! Thanks for following up with your solution. 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!