charting time series with milliseconds with scatter

[ecoulson]

I am trying to plot data dumped to csv file with time series so X Axis is the
time in following format (currently): Dec-1-2009 09:48:39.972 AM

I can change this format if it helps. The times are sample times of data
and are not uniform in interval. So chart needs to respect the the different
XAxis intervals between ticks. I update the data from csv file it looks
fine. I put it in a scatter - straight line - with markers. But I can't get
the XAxis correct. I can change the format of the datetime stamp in the csv
file - this would be no problem.

I have seen many false paths on the internet of someone asking similiar
questions. Anyone can direct me to a good source for charting scientific
type samples in milliseconds with excel 2007 - with a date that has the Month
Day Year hours min secs and milliseconds ?

Here is typical data:

Dec-1-2009 09:48:39.972 AM 203.85
Dec-1-2009 09:48:50.619 AM 212.28
Dec-1-2009 09:49:01.266 AM 189.59
Dec-1-2009 09:49:11.913 AM 206.41
Dec-1-2009 09:49:22.560 AM 209.3
Dec-1-2009 09:49:33.207 AM 196.99
Dec-1-2009 09:52:23.559 AM 196.77
Dec-1-2009 09:52:34.206 AM 198.09
Dec-1-2009 09:52:44.853 AM 199.59
Dec-1-2009 09:52:55.500 AM 203.87
Dec-1-2009 09:53:06.147 AM 193.87
Dec-1-2009 09:53:16.794 AM 188.1
Dec-1-2009 09:53:27.441 AM 212.21
Dec-1-2009 09:53:38.088 AM 182.68
Dec-1-2009 09:53:48.735 AM 212.35
Dec-1-2009 09:53:59.382 AM 210.95
Dec-1-2009 09:54:10.029 AM 210.79


Thanks , Eric

 

[Luke M]

I don't believe XL is recognizing that format as a date/time stamp, but
rather a text string (thus creating category labels in your chart). Perhaps
you could change your format to something like this:

Dec 1, 2009 09:48:39.972 AM

(Ironically, XL has no problem after it sees this as a number using a
display format of:
mmm-d-yyyy hh:mm:ss.000 AM/PM
to produce the same as your original input. Go figure!)

 

[ecoulson]

Hi Luke,

This is definitely on the right direction. I hand change in excel cell from
Dec-1-2009 09:48:39.972 AM
TO
Dec 1, 2009 09:48:39.972 AM
Excel will change it back to
Dec-1-2009 09:48:39.972 AM

but it sees it correctly in fx textbox.

However, since I am writing to a csv file I have to double quote the Dec 1,
2009 09:48:39.972 AM:

"Nov 2, 2009 03:23:49.162 PM",28.233,68.0
"Nov 4, 2009 03:24:22.294 AM",37.914,26.0
"Nov 5, 2009 03:24:55.426 PM",33.796,24.0
"Nov 7, 2009 03:25:28.558 AM",32.459,24.0
"Nov 8, 2009 03:26:01.690 PM",35.849,23.0
"Nov 10, 2009 03:26:34.822 AM",30.429,20.0
"Nov 11, 2009 03:27:07.954 PM",38.239,14.0
"Nov 13, 2009 03:27:41.086 AM",28.247,22.0
"Nov 14, 2009 03:28:14.218 PM",37.914,26.0

And then when I open the CSV file in excel the user would see:

23:49.2 28.233 68
24:22.3 37.914 26
24:55.4 33.796 24
25:28.6 32.459 24
26:01.7 35.849 23
26:34.8 30.429 20
27:08.0 38.239 14
27:41.1 28.247 22
28:14.2 37.914 26
28:47.3 37.843 19
29:20.5 34.231 25
29:53.6 37.37 19
30:26.7 31.445 15

which is recognised as data in fx textbox but without the milliseconds. At
least by hand editing it I was able to produce a scatter line graph with
times correct. Using custom number XAxis format mmm-d-yyyy hh:mm:ss.000
AM/PM. But obviously this would not work for a product that dumps a listview
- gridview to a csv file... as this would not be good user experience.

Thanks, Eric