Count the number of occurrences between In and Out times.

**allwrighty** · 04-20-2013, 10:43 AM

I have a scheduling spreadsheet that I created and need to be able to count the number of occurrences by hour between the In and Out times for each employee. E.g. How many occurrences are there between the In and Out times that = 8:00 AM, 9:00 AM etc. I want to get a count of how many employees are scheduled for each hour of the day that I can use in a line/bar graph.

Schedule.PNG

Employee In Out
John Doe 7:00 AM 3:00 PM
Jane Doe 9:00 AM 4:30 PM
Bill Smith 12:00 PM 8:00 PM
Sue Jones 8:00 AM 4:00 PM

**bebo021999** · 04-20-2013, 11:26 AM

Assuming schedule area is in range A2:C5
From A7 to X7: list of hour of day.
A7=1:00 AM
B7=A7+1/24
Drag across to X7
From A8 to X8: count employees each hour

Please Login or Register  to view this content.

Drag across to X8
Use A7:X8 as data area for the graph.

**allwrighty** · 04-20-2013, 12:53 PM

That is awesome!!! Exactly what I was looking for. Thank you

**allwrighty** · 05-01-2013, 05:18 PM

Thank you bebo021999 for your help with counting the number of occurrences. Now I need assistance with counting them when they span midnight. I have attached a sample spreadsheet. For example, when someone is scheduled to start at 10:00 PM on Monday and the end time is 5:00 AM on Tuesday I want to be able to have the occurrences counted on either side of midnight for the appropriate day so that it is reflected in the graph.