Graph number of concurrent events over time given start and end times

Excel__lent

New Member
Joined
Sep 1, 2015
Messages
4
Hello,

I have x number of sessions occurring noted by start times in column A and end times in column B in the format of "mm/dd/yyyy hh:mm".
Here is a sample of the data sorted by oldest time in column A first:

8/31/2015 22:01 8/31/2015 22:24
8/31/2015 22:01 8/31/2015 22:27
8/31/2015 22:01 8/31/2015 22:27
8/31/2015 22:01 8/31/2015 22:04
8/31/2015 22:01 8/31/2015 22:05
8/31/2015 22:02 8/31/2015 22:04
8/31/2015 22:02 8/31/2015 22:04
8/31/2015 22:02 8/31/2015 22:30
8/31/2015 22:02 8/31/2015 22:30
8/31/2015 22:02 8/31/2015 22:06
8/31/2015 22:08 8/31/2015 22:11
8/31/2015 22:10 8/31/2015 22:46
8/31/2015 22:18 8/31/2015 22:54
8/31/2015 22:28 8/31/2015 23:03
8/31/2015 22:29 8/31/2015 22:58
8/31/2015 22:30 8/31/2015 22:58
8/31/2015 22:32 8/31/2015 22:36
8/31/2015 22:33 8/31/2015 22:37
8/31/2015 22:40 8/31/2015 23:16
8/31/2015 23:03 8/31/2015 23:34
8/31/2015 23:06 8/31/2015 23:10
8/31/2015 23:13 8/31/2015 23:38
8/31/2015 23:13 8/31/2015 23:45
8/31/2015 23:42 9/1/2015 0:12
8/31/2015 23:42 9/1/2015 0:13
8/31/2015 23:44 9/1/2015 0:16
8/31/2015 23:55 9/1/2015 0:30
9/1/2015 0:14 9/1/2015 0:42
9/1/2015 0:15 9/1/2015 0:45
9/1/2015 0:20 9/1/2015 0:23
9/1/2015 0:27 9/1/2015 0:31
9/1/2015 0:34 9/1/2015 0:38
9/1/2015 0:42 9/1/2015 1:16
9/1/2015 0:47 9/1/2015 0:51
9/1/2015 0:53 9/1/2015 1:30
9/1/2015 1:05 9/1/2015 1:38
9/1/2015 1:21 9/1/2015 1:26
9/1/2015 1:33 9/1/2015 2:11
9/1/2015 1:37 9/1/2015 2:12
9/1/2015 1:42 9/1/2015 2:12

I need to create a line graph showing the number of concurrent sessions over time. I have seen many different ways to calculate concurrent sessions during each session listed, however, I can not figure out how to show how many concurrent sessions exist at a particular point in time; granularity down to the minute would be preferred.

In the above data set, I would expect to see 10 concurrent sessions at peak. I would like to graph the change in concurrent sessions with the number of sessions on the y-axis, and an interval of time on the x axis. For the example above, the x axis should show 22:00-03:00. Note that spanning past midnight into the next day is needed.
 
Last edited:

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
8/31/2015 22:01 8/31/2015 22:24
8/31/2015 22:01 8/31/2015 22:27
8/31/2015 22:01 8/31/2015 22:27
8/31/2015 22:01 8/31/2015 22:04
8/31/2015 22:01 8/31/2015 22:05
8/31/2015 22:02 8/31/2015 22:04
8/31/2015 22:02 8/31/2015 22:04
8/31/2015 22:02 8/31/2015 22:30
8/31/2015 22:02 8/31/2015 22:30 X
8/31/2015 22:02 8/31/2015 22:06
8/31/2015 22:08 8/31/2015 22:11
8/31/2015 22:10 8/31/2015 22:46 X
8/31/2015 22:18 8/31/2015 22:54
8/31/2015 22:28 8/31/2015 23:03
8/31/2015 22:29 8/31/2015 22:58
8/31/2015 22:30 8/31/2015 22:58
8/31/2015 22:32 8/31/2015 22:36
8/31/2015 22:33 8/31/2015 22:37
8/31/2015 22:40 8/31/2015 23:16

do you count the two I marked X as concurrent ?
 
Upvote 0
8/31/2015 22:01 8/31/2015 22:24
8/31/2015 22:01 8/31/2015 22:27
8/31/2015 22:01 8/31/2015 22:27
8/31/2015 22:01 8/31/2015 22:04
8/31/2015 22:01 8/31/2015 22:05
8/31/2015 22:02 8/31/2015 22:04
8/31/2015 22:02 8/31/2015 22:04
8/31/2015 22:02 8/31/2015 22:30
8/31/2015 22:02 8/31/2015 22:30 X
8/31/2015 22:02 8/31/2015 22:06
8/31/2015 22:08 8/31/2015 22:11
8/31/2015 22:10 8/31/2015 22:46 X
8/31/2015 22:18 8/31/2015 22:54
8/31/2015 22:28 8/31/2015 23:03
8/31/2015 22:29 8/31/2015 22:58
8/31/2015 22:30 8/31/2015 22:58
8/31/2015 22:32 8/31/2015 22:36
8/31/2015 22:33 8/31/2015 22:37
8/31/2015 22:40 8/31/2015 23:16

do you count the two I marked X as concurrent ?

The short answer is yes.

The first pair marked with an "X" is from 22:02 - 22:30 and the second from 22:10 - 22:46. This means that from 22:10 - 22:30, these two are concurrently active.

The graph I am trying to achieve would have a y-axis value of n at 22:09, then n+1 at 22:10 when the second starts, and then n again at 22:30 when the first session ends (if you disregard all other influence on n outside of these two sessions).
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top