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.

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.

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.