Graph usage for times of day
 

Hello all!

I've done some fairly advanced Excel development, but this one has me stumped...

I have a data set with "Time Start" and "Time Stop" columns for a work time tracking spreadsheet. I also have a "Duration" column if needed (timestop -timestart).

The times are all rounded to 15 minute increments, and the data set looks like this:

Time In Time Out Duration (hrs)
10:00 10:30 .5
10:30 13:15 2.75
13:15 16:30 3.25
10:00 10:45 .75
10:45 14:15 3.5
14:15 16:15 2
10:15 10:30 .25
10:30 13:15 2.75
13:15 16:30 3.25
10:00 10:45 .75
10:45 14:15 3.5
14:15 16:15 2

I want to graph the times being worked for a 24 hour day in order to see when a person is typically working or not.

X Axis: 24 hour day in 15 minute increments
Y Axis: # of instances (In the above data 10:00 would have 3, 16:30 would have 2, 13:00 would have 4)

This should show kind of a bell curve, with the earlier and later hours having few instances, curving up around 10:00, a dip around lunch time, and curving back down around 16:00.

I can create hundreds of columns and create formulas for each 15 minute increment in the day to determine where a start-stop time falls, but before I do that I wonder if there's an easier way.

Thank you!

~Brian