Excel Scatter Plot Has Incorrect Origin and Axis Range

[engineer47]

Hi there.

I am in the process of making a VBA program that automatically generates graphs based on an input from a data file.

I have been trying to create a graph from this imported data however the axis are displayed incorrectly.

The data in question is approximately 5000 cells, each of type "Time" and referring to a 2 minute time interval over several days.

E.g 1:06:00 PM, 1:08:00 PM, ....... 3:26:00 AM

As the data is over several days, several cells have the same value however the data is in a particular order, starting with the first cell at 1:06:00 PM and ending at 3:26:00 AM a few days later.

To get the basic code going i have plotted this column of data against itself, expecting a linear plot starting at the origin of (1:06:00 PM,1:06:00 PM) (x,y) and ending at (3:26:00 AM,3:26:00 AM) (x,y).

Instead what happens is the plot begins at 12:00:00 AM,12:00:00 AM and arbitrarily ends a day and a half later at 4:48:00 AM,4:48:00 AM as can be seen below

snag.gy/a42ybF.jpg[/url]

When checking the data series it is in the right order however.

snag.gy/uPWbsk.jpg[/url]

Can someone please tell me why the data on the axis starts at the wrong place and doesn't continue over the several day period it is meant to?

Thanks in advance

Edit: It wont let me post image link so add a url tag to the front and h t t p etc to start of link

[MrShorty]

The screenshots are not that helpful for debugging anyway. Better would be to upload a sample file to the forum (click on go advanced, find the "manage attachments" link below the editor, and use the file uploader).

The first thing I would look at if I had a file would be the exact values being stored in these "time" cells. Remembering that Excel stores time as a decimal number that represents a fraction of a day (and days since Jan1 1900 are the integer parts, so a single number can store both date and time information), I would first be looking to see if these times include time information only (are a value between 0 and 1 when formatted as general) or date and time information (other decimal number >=0 when formatted as general, If the dates are near present, I would expect numbers around 40000).

My first guess, because your X values seem to be between midnight and midnight (0 and 1??) is that the numbers only contain time/fraction of a day information, and that you need to add date information to the time -- even if it is as simple 0.xxx for the first day, 1.xxx for day 2, and 2.xxx for the third day.