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.