Plotting difference between logged sent-time / acknowledgment-time in Excel 2011

Question

I have an excel sheet which is a log containing the time that a message has been sent and the time that the corresponding acknowledgment for that message was received. It also contains the time between the sent-time and acknowledgment-time. It looks like:

msg_id    Sent-time                Acknowledgment-time        duration
1          2015-04-07 10:00:14      2015-04-07 10:00:15        00:00:01
2          2015-04-07 10:00:14      2015-04-07 10:00:16        00:00:02
3          2015-04-07 10:00:15      2015-04-07 10:00:15        00:00:00
4          2015-04-07 10:00:15      2015-04-07 10:00:18        00:00:03
6          2015-04-07 10:00:15      2015-04-07 10:00:19        00:00:04
... etc ...

I want to create a graph that gives insight into how the time between sending messages and receiving acknowledgments for those messages changes over-time. I wanted to make a scatter graph with on the x-axis the sent-times (continuous) and on the y-axis the duration-time. Where the duration of each message is a dot in the scatter plot.

However I just can't get the graph right in excel 2011. What graph should I use for this? I selected the sent-times column and the duration column and then click: graph->scatter but it gives me one dot with strange values on the y-axis. I formatted my sent-time column as time: "07/04/2015 10:00:11" and the duration as time: 10:00:11. I find nearly no settings for choosing what to place on which axis.

I would appreciate it if someone could show me how to do this. Tips on better ways to analyse what I want are also welcome.

EDIT for clarity I want to view how the time between the sending of the message and the receipt of the acknowledgment (which I call the duration) changes over time. So I want to know if messages that were sent at some point in time need to wait longer for an acknowledgment than messages that were sent at another point in time.

Answer 1

X/Y charts (scatter charts) aren't as intuitive to set up in Excel as other chart types.

Try the following:

1. Right-click on your chart and choose Select Data from the menu.
2. Remove all items from the left-hand side of the option menu
3. Add a new series
4. Give it a title
5. For the X-Values select your Sent-time data
6. For the Y-Values select your 'duration' data
7. Et voila!

Here is what mine looks like (obviously could use some formatting!)

You may as you do this, find that it could be difficult to decipher the data when there is a lot of data added. You may have more success by bucketing the data a bit (by hour, 15 minute increments, etc.) and apply some quartile calculations.

Good luck!