Hi all!
I'm trying to figure out if this can be done. I have data of employee schedules where each row has a start and an end time (along with department, location, manager, etc). I need to create a pivot table with half hour intervals (12am, 12:30am, 1am, etc) that counts the number of employees whose schedules overlap with each interval.
Currently, I have a static list of the intervals and COUNTIFS formulas set up for each interval with data validation dropdowns to allow the user to filter the results, but if I could get a pivot table, that would make filtering so much easier.
Example COUNTIFS formula currently in use: =COUNTIFS('Raw Data'!$D:$D,C$9,'Raw Data'!$M:$M,"<"&$A11,'Raw Data'!$N:$N,">="&$A10,'Raw Data'!$E:$E,$C$4,'Raw Data'!$H:$H,$C$5,'Raw Data'!$A:$A,$C$6)
Explanation: Count if Weekday matches column header, start time is less than interval end time, end time is greater than or equal to interval start time, Area matches dropdown, Location matches dropdown, and Department matches dropdown.
Bookmarks