Date/Time format

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

I'm trying to plot some information that comes from a webpage as two
columns of data - the first column is the time/date of the event
(mm/dd/yy hh:mm:ss) and the second column is the value of the
transaction. I simply highlight the part of the table I want to import
from the website and copy it to an Excel sheet. Although the data
looks good in the spreadsheet (date and time with a space between)
when I plot that information I get either the time or the date, but
not both. If I use the mm/dd/yy hh:mm:ss format, I get the dates ok,
but the times are all 00:00. Any ideas?
Thanks
steve mcalilster
 
Steve said:
I'm trying to plot some information that comes from a webpage as two
columns of data - the first column is the time/date of the event
(mm/dd/yy hh:mm:ss) and the second column is the value of the
transaction. I simply highlight the part of the table I want to import
from the website and copy it to an Excel sheet. Although the data
looks good in the spreadsheet (date and time with a space between)
when I plot that information I get either the time or the date, but
not both. If I use the mm/dd/yy hh:mm:ss format, I get the dates ok,
but the times are all 00:00. Any ideas?
Thanks
steve mcalilster
Click to expand...

I suspect the cell containing the date/time is formatted as text. If your
date/time is in cell A1, go to another cell and enter the formula
=ISNUMBER(A1). If it returns FALSE, then the cell does not contain a
number.

Assuming the cell is formatted as text, you might use the value function to
retrieve the date/time. Again assuming your date/time is in A1, enter the
formula =VALUE(A1) in another cell. Format that cell as mm/dd/yy hh:mm:ss
and see if the result is as you wish. If so, copy the cell and Paste
Special | Values back into cell A1.

Dave
dvt at psu dot edu
 
Dave

Thanks for the help - yes, the field does return a TRUE to the NUMBER
formula - any other ideas?
steve
 
Steve said:
Dave

Thanks for the help - yes, the field does return a TRUE to the NUMBER
formula - any other ideas?
steve
Click to expand...

If you format that cell as a number, what do you get? Also tell us the
corresponding date/time.

From your original post:
...when I plot that information I get either the time or the date, but
not both. If I use the mm/dd/yy hh:mm:ss format, I get the dates ok,
but the times are all 00:00.
Click to expand...

Where are these time/dates displayed? As axis labels? Or data labels? Or
other?

If they are on the axis, try this step. Select the axis, Format | Selected
axis | Scale tab. Is it a value or time-scale axis? If yes to either one,
change the major unit to a non-integer number of days (i.e. 0.5 days or 12
hours). Now does the time still show as 0:00 when using a mm/dd/yy hh:mm:ss
format?

Dave
dvt at psu dot edu
 
Steve -

Line chart with a time scale axis, right? They should call it a date
scale axis, because Excel plots any time for a given date at midnight of
the beginning of the date, thus 0:00.

You can use an XY Scatter chart, which uses continuous time data, not
just the date data, when plotting the X values. You give up some of the
nice axis formatting of the time scale line charts, unfortunately.

- Jon
 
Dave

The cell contains 2/11/03 2:09 PM - when I format it as a number, I
get 142,076 -

steve
 
Dave

I'm very sorry, I was looking at the wrong column - when I plot the
2/11/03 2:09PM value as a number, I get 37663.5896

steve
 
Jon

Your comments made me realize that I was asking the wrong question -
what I was trying to do is have the time of the data print on the
x-axis, next to the date of the data. But I don't really need to
"plot" the time on the x-axis. So I simply printed the values next to
the point, and that accomplishes what I was looking for. Thanks for
jarring me into seeing what I was doing wrong.

Now, I do have another problem. The y-axis data is from a web page and
is in a strange format. It is two values separated by a comma (this
is blood pressure data, so you have systolic and diastolic data for
each reading). And the data comes in as 134.080 for "134 over 80." How
can I separate those two values and plot them separately on the
y-axis? That is, I would have two lines, one for systolic pressure and
one for diastolic. I have a graph like this where I manually entered
the data, and it works fine, but this information is already
formatted. Right now, it plots as 134,080 (one hundred thirty-four
thousand eighty) - is there a way to separate the values? Maybe split
the column into two new columns and plot them? I'd appreciate any
ideas -

Thanks,

steve
 
Now, I do have another problem. The y-axis data is from a web page and
is in a strange format. It is two values separated by a comma (this
is blood pressure data, so you have systolic and diastolic data for
each reading). And the data comes in as 134.080 for "134 over 80." How
can I separate those two values and plot them separately on the
y-axis? That is, I would have two lines, one for systolic pressure and
one for diastolic. I have a graph like this where I manually entered
the data, and it works fine, but this information is already
formatted. Right now, it plots as 134,080 (one hundred thirty-four
thousand eighty) - is there a way to separate the values? Maybe split
the column into two new columns and plot them? I'd appreciate any
ideas -
Click to expand...

It's good to hear that you solved problem nubmer one.

As for problem number two, I think the easiest solution is to do a little
basic math in the worksheet. With data 134,080 in cell A1, put this in
another cell to get 134: =FLOOR(A1/1000,1). And to return 80, use the
formula =A1-FLOOR(A1,1000). Now plot the new cells.

Dave
dvt at psu dot edu
 
Wow... I've never used the FLOOR function, but I'm sure it'll do what
you say it will - thanks very much for your help!

steve
 
Back
Top