Time and Date on X axis

[DataMan]

We use data files to troubleshoot and anaylise temperature, flow,
pressure, etc. trends over time and date. Sometimes these files will
contain as much as 20,000 data points over days or months of time.

Excel 97 automaticly scales the x axis of a line chart if you have the
Date and Time selected in your data. Excel 2000 and XP does not scale
both categories and tries to plot every data point in the chart. You
cannot scale this line in the newer versions because this item is
grayed out on the Scale tab. Your chart ends up with a black blob at
the bottom where the x axis is and it slows the computer down trying to
plot all of this data graphically.

MS confirmed that this feature was taken out of the newer versions.
They did not seem concerned with fixing it either. Because of this,
many of us at work are still using 97.

If you only select the time, things will chart fine but then you have
to count over the days to figure out what the date is.

Has anybody ran into this problem and have a work around?

 

[Jon Peltier]

Keep the date and time together in one column, or insert a column and
add the date to a time. Excel treats dates as whole numbers and times
as the fraction of a day between midnight of one day and the next. Now
use this combined date-time as the X value of an XY Scatter chart, not a
line chart.

- Jon

 

[DataMan]

I am trying to setup a macro that could take the Time in one column and
Date in another column and merge them into one. This is so I could
chart our trends with Excel XP as mentioned above.

How can this be acomplished? I started my recording and inserted a new
column. I copied the date from the date column. I cannot copy/paste the
time at the end of the date in the new column.

The dates and time are always different so the macro needs to be smart
enough to look at the two columns row by row and merge the two values.

 

[DataMan]

I used the following to get the Date and Time together.

=concatenate(a2,b2)

=a2 & b2 works also but I like the other better.

Both work fine but they change the date to the real number of 37878.
The time shows okay. No amount of formatting can change the date number
back to mm/dd/yyyy.

XY Scatter charts still don't work as good as line charts when plotting
this data.

I am about to give up on this venture and just use good ole Excel 97
for all of my troubleshooting graphs. It's quick and easy and works
great.

Anybody else have anymore ideas I would be happy to hear.

 

[Jon Peltier]

DataMan -

To maintain the numerical values, use A2+B2, which adds the whole number
(the date) and the fraction (the time). Since it is still a number, you
can apply a number format like mm/dd/yyyy.

Concatenation puts together the text representations of the time and
date, and Excel only sees it as a string. So you can't format the
number, and a scatter chart will have an improper X axis.

- Jon

 

[dvt]

I used the following to get the Date and Time together.

=concatenate(a2,b2)

=a2 & b2 works also but I like the other better.

Both work fine but they change the date to the real number of 37878.
The time shows okay. No amount of formatting can change the date
number back to mm/dd/yyyy.

It looks like you missed part of the formula. Allow me to repeat:
=VALUE(A2&B2)
That assumes that A2 and B2 are formatted as text. If the cells are
formatted as date/time, use A2+B2 as Jon suggested.

Excel 97 automaticly scales the x axis of a line chart if you have the
Date and Time selected in your data. Excel 2000 and XP does not scale
both categories and tries to plot every data point in the chart. You
cannot scale this line in the newer versions because this item is
grayed out on the Scale tab. Your chart ends up with a black blob at
the bottom where the x axis is and it slows the computer down trying
to plot all of this data graphically.

And followed up with:

XY Scatter charts still don't work as good as line charts when
plotting this data.

I can't replicate your problem using XP. If I create a line chart with
dates along the x-axis, I can choose the "scaling." Select the x-axis,
Format | Selected axis | Scale tab. I can change the major unit to whatever
I choose. Either I don't understand the problem or you have something else
going on....

Dave
dvt at psu dot edu

 

[DataMan]

If you have one column with Dates, say column A, and the next column
with the Time. This is where 97 works but 2000/XP does not.

The csv files we work with are always output in this format. We
sometimes pull trends with weeks worth of data at 30 second intervals.
The file can have as many as 20,000 rows and 10 or so columns. When
graphed with a line chart, Excel 97 automaticly scales the X axis with
the time as the top of the axis and the date at the bottom. It does a
nice job.

2000/XP wants to plot all of the times in the x axis. The scale page's
boxes are grayed out and it will not let you adjust it.

MS has confirmed with me that this is a feature that was removed from
2000. They did not say that it would be fixed anytime. My only hope is
that our company may work with MS and get them to write a patch. Our IT
department expects to be consulting with them in November.

 

[DataMan]

I entered

=Value(a2&b2)

I get an error regardless the format of the cells.

=A2+B2

Same thing. I get an error.

=sum(a2:b2)

No workie

 

[Jon Peltier]

Apparently the cells are formatted as text, and Excel doesn't recognize
the date or time format. What do the cell entries look like (i.e.,
paste a few here, so we can help you parse them into real date-time values)?

- Jon

 

[DataMan]

Here you go.

Date Time
9/14/2003 00:00:00
9/14/2003 00:00:30
9/14/2003 00:01:00
9/14/2003 00:01:30
9/14/2003 00:02:00


This is an exact copy/paste. Date is column A and Time is column B.

I've also attached a picture of a small portion of one of the test
files that I'm working with. This is the first time for me to try this
so I hope it work okay.

I don't see my picture on the forum. Not sure how to do this either. I
will try again.

 

[Jon Peltier]

DataMan -

I put your data into columns A and B, formatted as text to simulate the
worst. I put this formula in B2 and filled it down:

=A2+B2

It implicitly converted the text values into date and time values:

9/14/03 12:00:00 AM
9/14/03 12:00:30 AM
9/14/03 12:01:00 AM
9/14/03 12:01:30 AM
9/14/03 12:02:00 AM

I suspect ExcelForum blocks attachments in text groups like this. I'm
not sure if a picture would increase our understanding.

- Jon

 

[DataMan]

You put the formula in B2?

I've been trying to write it in C2. Maybe that has been my problem all
along.

I will try it when I get to work tomorrow. I have good old 97 at home
and I don't have any problems with it graphing.

Thanks, I'll let you know if it works for me.

 

[dvt]

You put the formula in B2?

I'll bet that was a typo on Jon's part. I'm quite sure he meant C2, not B2.

Dave
dvt at psu dot edu

 

[DataMan]

Yes, I did not think about it last night but that does create a circular
reference.

I don't what the problem is because =A2+B2 written in the C column
still does not work for me this morning no matter what the format of
the cells is. It returns "Value" error.

 

[dvt]

Yes, I did not think about it last night but that does create a
circular reference.

I don't what the problem is because =A2+B2 written in the C column
still does not work for me this morning no matter what the format of
the cells is. It returns "Value" error.

I have found one way to replicate your error. If I enter a nonsensical date
as text in column A, I get the #VALUE error in C. So, for example, if I
enter '1/42/2003 (including the single quote) in A2, C2 says #VALUE.

Is it possible that your dates are entered as text and use a different
format than Excel seeks? Perhaps your dates are entered in dd/mm/yy format
while Excel wants mm/dd/yy? I can, for example, get the error if I enter
15/12/2003, which is valid in dd/mm/yyyy but not in mm/dd/yyyy.

Dave
dvt at psu dot edu

 

[Jon Peltier]

My bad. The formula was in C2.

- Jon

 

[DataMan]

The program that I use to pull the data from our SCADA system apparently
inserts a space character before the time. When pulling the csv file
into Excel it brings this same space character into the time column
that precedes every time entry.

Remove the space and the addition works. Yea!

I will play with this at work Monday and see what I can do to remove
the spaces before doing the math.

One thought. This space character has never triped up Excel 97 before.
I wonder whats up with that?

Thanks for all the help. I will let you know how it goes.