Count ID for each hour (for ex. number of employees in the office by the hour)

[adias102]

Hi,

I need to create a Pivot table that shows how many employees are at the office by day of week and hour, regardless of the month and year. That is, empA arrived at 01/01/2021 20:35 and left at 02/01/2021 09:05 (dd/mm/yyyy hh:mm) so I need to see that for each hour between friday at 20:00 and saturday at 10:00 there was 1 employee at the office.

My data is basically:
EmpID             123
StartTime       01/01/2021 20:35:00.000
EndTime         02/01/2021 09:05:00.000
and the rest of the information....

For each employee i have the attendance time for the last few years.
Since we are a 24/7 organiztaion, the time between StartTime and EndTime can be over 24h.
For example: StartTime 04/04/2021 08:00, EndTime 06/04/2021 09:00 (dd/mm/yyy hh:mm) and I need to see 1 employee at the office from sunday at 08:00 untill tuesday at 09:00.

		day of week
from hour	to hour	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
00:00	01:00							1
01:00	02:00							1
02:00	03:00							1
03:00	04:00							1
04:00	05:00							1
05:00	06:00							1
06:00	07:00							1
07:00	08:00							1
08:00	09:00							1
09:00	10:00							1
10:00	11:00
11:00	12:00
12:00	13:00
13:00	14:00
14:00	15:00
15:00	16:00
16:00	17:00
17:00	18:00
18:00	19:00
19:00	20:00
20:00	21:00						1
21:00	22:00						1
22:00	23:00						1
23:00	00:00						1

I would appreciate any suggestions.

[Dalton_Ruer · Accepted solution]

You need to use the IntervalMatch function, this will jump to help for it. 

I have attached an application that demonstrates it with example of minute by minute analysis, and it needs the Master Calendar QVD that the other application generates. But you can just copy the code out of the minute calendar application instead of generating the QVD if you wish. The simply change it to be hourly instead. 

[adias102]

Thank you, it was very helpful 🙂