Hellsteeth
New Member
- Joined
- Dec 5, 2018
- Messages
- 5
Hello,
I have a data set with over 60,000 lines of data per year. It contains the date and time an object was placed in a specific container and for how long it was in the container.
The more objects that were simultaneously together in the container the greater the weight and loading on the system.
I want to calculate the loading on each container to see what date and times the maximum loading occurred.
I have been trying to use both pivot tables or sumproduct along the lines of other posts in this forum such as =SUMPRODUCT(--($B$1:$B$60000<=B1),--($C$1:$C$60000>=B1)) I am however having difficulty adapting it to take into account the containers and dates.
Here is a sample of the data with some expected outcomes in the last two columns.
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
I have a data set with over 60,000 lines of data per year. It contains the date and time an object was placed in a specific container and for how long it was in the container.
The more objects that were simultaneously together in the container the greater the weight and loading on the system.
I want to calculate the loading on each container to see what date and times the maximum loading occurred.
I have been trying to use both pivot tables or sumproduct along the lines of other posts in this forum such as =SUMPRODUCT(--($B$1:$B$60000<=B1),--($C$1:$C$60000>=B1)) I am however having difficulty adapting it to take into account the containers and dates.
Here is a sample of the data with some expected outcomes in the last two columns.
A | B | C | D | E | F | G | |
---|---|---|---|---|---|---|---|
1 | Start Date | Start Time | End Date | End Time | Container | Simultaneous | Overlap (mins) |
2 | 01/01/2017 | 9:15 | 01/01/2017 | 11:20 | 2 | 0 | 0 |
3 | 01/01/2017 | 16:30 | 01/01/2017 | 19:25 | 2 | 0 | 0 |
4 | 01/01/2017 | 14:00 | 01/01/2017 | 18:10 | 4 | 0 | 0 |
5 | 01/01/2017 | 16:40 | 01/01/2017 | 17:15 | 10 | 2 | 30 |
6 | 01/01/2017 | 16:45 | 01/01/2017 | 18:00 | 10 | 2 | 30 |
7 | 01/01/2017 | 18:05 | 01/01/2017 | 18:20 | 10 | 0 | 0 |
8 | 01/01/2017 | 17:50 | 01/01/2017 | 20:30 | 8 | 0 | 0 |
9 | 02/01/2017 | 15:45 | 02/01/2017 | 17:55 | 2 | 2 | 10 |
10 | 02/01/2017 | 17:45 | 02/01/2017 | 20:00 | 2 | 2 | 25 |
11 | 02/01/2017 | 19:45 | 02/01/2017 | 21:55 | 2 | 2 | 15 |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2
Any guidance or ideas will be kindly received.