Time and Date on X axis

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.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

DataMan wrote:
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.

Since you're using a helper column anyway, why not do it in the spreadsheet?
Assuming your dates are in column A and times in column B, put this in C1:

= VALUE(A1 & B1)

Now format cell C1 as Time (i.e. m/d/yy h:mm). Sorry, I'm not good with VB
so I don't know how to code this.

Dave
dvt at psu dot edu

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.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

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
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

DataMan wrote:
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.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

DataMan wrote:
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.

In his original post, DataMan wrote:
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

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



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

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
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

DataMan wrote:
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

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
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

DataMan wrote:
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.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Jon Peltier wrote:
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

DataMan wrote:
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 wrote:
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