How to combine time interval data on excel

Question

I have a set of data for internet usage for two people. For each person I have each time they logged in and out of the internet over a day. I want to make a bar chart/histogram where the x axis is time and the width of the bar is how long each time interval they log in for is. I want the data for each person shown on the same graph with a different colour for each person. How can I do this in excel?

Example data:

Person 1
Connected    Disconnected
08:54:05     08:56:02
09:16:35     09:18:24
09:31:54     09:35:11

Person 2
Connected    Disconnected
08:52:46     08:53:34
09:04:12     09:17:35
09:31:23     09:35:14

Answer 1

Here's how to get what you want.

You need to rearrange your data as shown below. I put person 1 and 2 data in adjacent columns. I put the connected times first, the disconnected times third, and the durations second, where the durations are simply the disconnected times minus the connected times.

I created Chart 1, a stacked bar chart, using the gold-shaded range. I had to switch rows and columns to get the appropriate orientation.

I created Chart 2 by formatting the connected bars with no fill, so they are not visible.

The blue-shaded ranges are where I calculated the initial time (8:00, or 1/3 of a day, 0.3333...) and the time interval (15 minutes or 0.0104666...) for the X axis. Essentially I typed the times I wanted in the upper of each pair of cells, and used =J2 and =J5 to get the second of each pair, but the second was formatted as a number instead of a time. I formatted the X axis and applied these to the horizontal axis scale of Chart 3.

Finally I made two adjustments to the vertical axis. First, I change the Axis Type from Automatic to Time Scale. This aligned all bars for 1 and all bars for 2. Then I applied a custom number format of "Person "0 so they appear as Person 1 and Person 2 in Chart 4.