collecting data between dates
Hello,
I am trying to cross-reference a sheet and collect data based on multiple criteria, including whether a date falls between a date range.
Ex: I have team members allocated to multiple project at different percentages for different dates. I am trying to pull the % allocated based on the team members name, the client and whether or not the period falls between a date range.
I want to pull % Allocation into a new sheet if the Assigned to and Client columns match my criteria and if the the week of Dec 11, 2023 to Dec 15th, 2023 fall between the Start Date and End Date.
Answers
-
Hi @Ashley1987 you can do that with an INDEX COLLECT function combination:
=INDEX(COLLECT({% Allocation}, {CLIENT}, Client@row, {Assigned to}, [Assigned to]@row, {Start date}, >=DATE(2023,12,11), {End date}, <=DATE(2023,12,15)),1)
-
Hi Lucas,
I tried your formula but got an error message #invalid value.
I need to be more specific. I am actually looking to pull the average allocation % for a date range per resource since some people work on multiple clients at the same time. This information will be pulled from the data sheet and put into the reporting sheet.
Ex: What % allocated is John Doe from Dec 11 to Dec 15?
Answer: 75% - this is the information i need to show up in the reporting sheet
Data sheet:
Reporting sheet:
-
Was anyone able to help me with this?
-
Hi @Ashley1987 we can’t see that you reply if you don’t @ mention us. If you need to get the average of something based on specific criteria, you’ll want to use the function combination AVG/COLLECT
Here’s a link to all the functions with links to description articles:
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!