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
|
|||
|
|||
Text To Columns - Date Conversion
Problem: "Text To Columns" converts some dates correctly then skips three
years (From 2001 to 2003). In a blank worksheet, I'm bringing in a text page from a Federal Reserve Bank site using a macro I created from Data/Get External Data/NEw Web Query. Here's the site I'm going to: http://www.federalreserve.gov/releas...cc_hist_fc.txt After everyting lands in one column I highlight column A and hit the Data/Text To Columns button, select Fixed Width, then remove the line that separates the Month from the Date on the next page, then Finish. That second to the last step is probably where my error occurs but I don't really know. The net result is all data after Dec-00 turns to 2004 when I try to chart the rsults. I've tried reformating the Date colum to M-Y but nothing works. I do notice thate there's a format change in the raw data from Jan-01 to present. It goes from Dec-00 to 1-Jan. If anyone has time to pull this up and see first hand, I'd be very grateful. Thanks. |
#2
|
|||
|
|||
Text To Columns - Date Conversion
John -
Here's what is happening. You are only providing two pieces of information, when Excel needs three to accurately pin down a date. Dec-99 is obviously Dec 1999 and Dec-00 is Dec 2000, but Dec-01 is interpreted December 1st 2004. Here's my suggestion. Import the text file into Excel. I just pasted the URL into the File Open dialog, and in your macro you can use Workbooks.Open("http://www.federalreserve.gov/releases/g19/hist/cc_hist_fc.txt") Keep the month and two-digit year in separate columns, then insert a new column A. The first row of data is row 8, so in cell A8, enter this formula and fill it down: =DATE(IF(C810,2000+C8,1900+C8),MATCH(B8,$B$8:$B$1 9,0),1) $B$8:$B$19 is the first set of months Jan to Dec, and the MATCH statement uses this to convert the month in each row into an index from 1 to 12. The IF statement converts the year to 4 digits, and the 1 means the first of each month. You can either keep the formula intact, or copy it and paste special as text then delete the month and two digit year columns. You could also make this conversion within the macro, using VBA math, but I think the macro is easier if you record the steps above while you do it once, and then incorporate the recorded code into your macro. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ John Gregory wrote: Problem: "Text To Columns" converts some dates correctly then skips three years (From 2001 to 2003). In a blank worksheet, I'm bringing in a text page from a Federal Reserve Bank site using a macro I created from Data/Get External Data/NEw Web Query. Here's the site I'm going to: http://www.federalreserve.gov/releas...cc_hist_fc.txt After everyting lands in one column I highlight column A and hit the Data/Text To Columns button, select Fixed Width, then remove the line that separates the Month from the Date on the next page, then Finish. That second to the last step is probably where my error occurs but I don't really know. The net result is all data after Dec-00 turns to 2004 when I try to chart the rsults. I've tried reformating the Date colum to M-Y but nothing works. I do notice thate there's a format change in the raw data from Jan-01 to present. It goes from Dec-00 to 1-Jan. If anyone has time to pull this up and see first hand, I'd be very grateful. Thanks. |
#3
|
|||
|
|||
Text To Columns - Date Conversion
Now why didn't I notice that! Thanks a million, Jon... especially for the
solution to fix it. "Jon Peltier" wrote in message ... John - Here's what is happening. You are only providing two pieces of information, when Excel needs three to accurately pin down a date. Dec-99 is obviously Dec 1999 and Dec-00 is Dec 2000, but Dec-01 is interpreted December 1st 2004. Here's my suggestion. Import the text file into Excel. I just pasted the URL into the File Open dialog, and in your macro you can use Workbooks.Open("http://www.federalreserve.gov/releases/g19/hist/cc_hist_fc.t xt") Keep the month and two-digit year in separate columns, then insert a new column A. The first row of data is row 8, so in cell A8, enter this formula and fill it down: =DATE(IF(C810,2000+C8,1900+C8),MATCH(B8,$B$8:$B$1 9,0),1) $B$8:$B$19 is the first set of months Jan to Dec, and the MATCH statement uses this to convert the month in each row into an index from 1 to 12. The IF statement converts the year to 4 digits, and the 1 means the first of each month. You can either keep the formula intact, or copy it and paste special as text then delete the month and two digit year columns. You could also make this conversion within the macro, using VBA math, but I think the macro is easier if you record the steps above while you do it once, and then incorporate the recorded code into your macro. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ John Gregory wrote: Problem: "Text To Columns" converts some dates correctly then skips three years (From 2001 to 2003). In a blank worksheet, I'm bringing in a text page from a Federal Reserve Bank site using a macro I created from Data/Get External Data/NEw Web Query. Here's the site I'm going to: http://www.federalreserve.gov/releas...cc_hist_fc.txt After everyting lands in one column I highlight column A and hit the Data/Text To Columns button, select Fixed Width, then remove the line that separates the Month from the Date on the next page, then Finish. That second to the last step is probably where my error occurs but I don't really know. The net result is all data after Dec-00 turns to 2004 when I try to chart the rsults. I've tried reformating the Date colum to M-Y but nothing works. I do notice thate there's a format change in the raw data from Jan-01 to present. It goes from Dec-00 to 1-Jan. If anyone has time to pull this up and see first hand, I'd be very grateful. Thanks. |
Thread Tools | |
Display Modes | |
|
|