Looking for a formula to populate the Sprint Number based off of the Start and End Dates
Hello,
I have a project plan and each task has a start and end date. I would like to determine which Sprint the task will be worked on based on the date range. (It is possible that a task can be worked on for multiple sprints ,and if possible, I would like to know all of the Sprint numbers for that particular task.
I have a separate sheet called Sprint Reference with each Sprint's date range (see below)
And I would like to have the Sprint column (shown below) to have all of the relevant sprint numbers based on the date range of the task.
I was trying to do this with an Index Collect formula but I kept receiving the "unparesable error"
Can any one hel; me to figure out how to do this?
TIA
Answers
-
Try a JOIN/COLLECT along the lines of:
=JOIN(COLLECT({Reference Table Sprint Column}, {Reference Table Start Column}, @cell < = Finish@row, {Reference Table Finish Column}, @cell >= Start@row), CHAR(10))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 464 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!