Excel 2011 plotting difference between logged sent-time / acknowledgment-time

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.

Answer 1

A simple XY scatter should be good for this. I am able to get a decent looking chart with your subset of data.

A couple of comments:

• I inserted a simple XY scatter with points only.
• Be sure that you are formatting the dates as dates in Excel. This helps the chart interpret the data correctly.
• The duration can also be a time (as below) or can be a number. If you are doing subtraction on the dates, your result will be a date itself. Bear in mind that dates have units of days so your seconds will be a small decimal in there. Formatting as a time allows the chart to render those small amounts correctly.

If the chart looks worse with more data, let me know. As of now it looks like you just need to wrangle the formats correctly so Excel is working with instead of against you.