If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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/ |
#2
|
|||
|
|||
Time and Date on X axis
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 |
#3
|
|||
|
|||
Time and Date on X axis
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/ |
#4
|
|||
|
|||
Time and Date on X axis
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/ |
#5
|
|||
|
|||
Time and Date on X axis
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 |
#6
|
|||
|
|||
Time and Date on X axis
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/ |
#7
|
|||
|
|||
Time and Date on X axis
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 |
#8
|
|||
|
|||
Time and Date on X axis
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/ |
#9
|
|||
|
|||
Time and Date on X axis
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 |
#10
|
|||
|
|||
Time and Date on X axis
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 |
|
Thread Tools | |
Display Modes | |
|
|