OK so I think I'm making this more complicated than it should be...
I have a list of data like the below (Table 2) and a separate table with the code opening and closing times which vary by day of the week (Table 1).
I'm looking to get a list of unique codes with the totals falling between the opening and closing times and a separate column for data that falls outside the opening and closing times each day. Eg.
Code | Total between times/day | Total outside of opening times.
123 xxx xxx
456 xxx xxx
Any help with the formula would be greatly appreciated.
Thanks
Table 1
<tbody>
</tbody>
Table 2
<tbody>
</tbody>
I have a list of data like the below (Table 2) and a separate table with the code opening and closing times which vary by day of the week (Table 1).
I'm looking to get a list of unique codes with the totals falling between the opening and closing times and a separate column for data that falls outside the opening and closing times each day. Eg.
Code | Total between times/day | Total outside of opening times.
123 xxx xxx
456 xxx xxx
Any help with the formula would be greatly appreciated.
Thanks
Table 1
Code | Mon | Tue | Wed | Thur | Fri | Sat | Sun | Mon | Tue | Wed | Thur | Fri | Sat | Sun |
123 | 10:00 | 10:00 | 10:00 | 10:00 | 10:00 | 09:00 | 16:00 | 22:00 | 22:00 | 22:00 | 22:00 | 23:00 | 23:30 | 22:00 |
456 | 10:00 | 10:00 | 10:00 | 10:00 | 10:00 | 09:00 | 11:00 | 22:00 | 22:00 | 22:00 | 22:00 | 23:00 | 23:30 | 22:00 |
789 | 11:00 | 11:00 | 11:00 | 11:00 | 11:00 | 09:00 | 12:00 | 22:00 | 22:00 | 22:00 | 22:00 | 23:00 | 23:30 | 22:00 |
<tbody>
</tbody>
Table 2
Code | Day | 00:30 | 01:00 | 01:30 | 02:00 | 02:30 | 03:00 | 03:30 | 04:00 | 04:30 |
123 | Mon | 2.6 | 2.6 | 2.8 | 1.8 | 1.7 | 2.0 | 3.1 | 4.2 | 1.7 |
123 | Tue | 2.3 | 3 | 6 | 7 | 7 | 8 | 11 | 10 | 7 |
123 | Wed | 2 | 4 | 5 | 7 | 8 | 7 | 15 | 8 | 2 |
456 | Mon | 3 | 4 | 5 | 9 | 8 | 6 | 14 | 8 | 3 |
456 | Tue | 3 | 3 | 4 | 7 | 8 | 9 | 26 | 8 | 2 |
456 | Wed | 2 | 3 | 6 | 5 | 8 | 10 | 20 | 8 | 1 |
789 | Mon | 2 | 3 | 4 | 5 | 5 | 10 | 31 | 20 | 6 |
789 | Tue | 2 | 3 | 5 | 2 | 5 | 18 | 12 | 4 | 6 |
789 | Wed | 2 | 3 | 6 | 5 | 4 | 10 | 21 | 4 | 6 |
<tbody>
</tbody>