Backstory: I work for a staffing company who is recruiting/managing people who work on the phone at home. We need to report back to the client on how many people we have scheduled at every interval of time and how many people actually worked during those same intervals.
We recently introduced a new online scheduling tool (Deputy), which is a dream, but the report features are lacking.
So I hired someone to use the API and export the live data into excel. Now I need to make something of it and I'm not exactly sure how to best go about it.
I've attached pictures of the raw data for just the schedule. Start Time and End Times of the shifts are in milliseconds of the day. I need to be able to filter it for the date, and the organizational unit (or department), then count how many shifts were scheduled. Our intervals are 15minutes long, so the first one of the day would be from 00:00 until 00:15, then 00:15-00:30, etc. I have also attached the desired end result table.
I have tried SUMPRODUCT, COUNTIF, ARRAYs. But I don't get what I need. While my excel work is strong, pivot tables confuse me and I don't know if that is the option here? I have access to SharePoint, Flow and Power BI if anyone thinks these could help.