Today I found to my consternation that my broker's updated web pages
have changed the format of the CSV files I download daily. I have
to re-work some complex formulas. On my way to accomplishing that,
I find one change is that what had been four-digit years are now
two-digit years. My Excel 2002 under XP isn't recognizing them as
dates. Is there an easy fix here?

Thanks,
Dallman

From Excel Help:

If you are using Microsoft Windows 2000 or later, the Regional Options in Windows Control Panel controls how Excel interprets
two-digit years.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Dallman Ross" <dman@localhost. wrote in message ...
|
| Today I found to my consternation that my broker's updated web pages
| have changed the format of the CSV files I download daily. I have
| to re-work some complex formulas. On my way to accomplishing that,
| I find one change is that what had been four-digit years are now
| two-digit years. My Excel 2002 under XP isn't recognizing them as
| dates. Is there an easy fix here?
|
| Thanks,
| Dallman

In , Niek Otten
spake thusly:

From Excel Help:

If you are using Microsoft Windows 2000 or later, the Regional
Options in Windows Control Panel controls how Excel interprets
two-digit years.

Niek, thanks, but I'm not sure that helps in this case. The
settings I have in there are the default for a U.S.-localized
version of Excel 2002. It says to interpret a two-digit year
as between 1030 and 2029, and that's fine.

My problem is, the entry M/dd/yy in the CSV file seems to
Excel to be a non-date string. I have formatted the column
for dates -- that makes no difference.

Dallman

In , Dallman Ross <dman@localhost.
spake thusly:

My problem is, the entry M/dd/yy in the CSV file seems to
Excel to be a non-date string. I have formatted the column
for dates -- that makes no difference.

Oh, brother; I see. The jerks inserted a blank space in front now
in all the columns in the CSV file. I'm not sure yet what the best
way to handle this is -- I'm not wanting to massage the data I
download and import.

Dallman

Maybe you can
select that range (column?)
edit|Replace
what: (spacebar)
with: (leave blank)
replace all

or even select that column (one column at a time)
data|text to columns


Dallman Ross wrote:

In , Dallman Ross <dman@localhost.
spake thusly:

My problem is, the entry M/dd/yy in the CSV file seems to
Excel to be a non-date string. I have formatted the column
for dates -- that makes no difference.

Oh, brother; I see. The jerks inserted a blank space in front now
in all the columns in the CSV file. I'm not sure yet what the best
way to handle this is -- I'm not wanting to massage the data I
download and import.

Dallman

--

Dave Peterson

In , Dave Peterson
spake thusly:

Maybe you can
select that range (column?)
edit|Replace
what: (spacebar)
with: (leave blank)
replace all

or even select that column (one column at a time)
data|text to columns

Thanks. The whole thing is a pain, because for example the names
of securities in Column A all now start with a leading space.
I re-imported the file and selected space as one of the optional
field delimiters, and checked the tickbox to use multiple
contiguous instances of a field as one; but still, the spaces
are there after import -- because the dodo who formatted the
data now has the header-row names shifted one character to the
left. So we get:

Description Quantity Date ...
Advanced Micro Devices Inc 90 6/8/06 etc.

Actually, re-initiating the data import does allow me to have the
dates be read as dats. So that's very good. But the leading
space before the security names is completely messing up my
lookup tables.

Whoever the bozo is who suddenly decided to alter the internal format
of CSV files downloaded by many thousands of customers with good
frequency ought to have his head examined. (To the good, they
finally fixed a spelling error that had "Purchase Price" written
without the final "e". However, now I just noticed the header fields
are not importing right, because of the offset of one space character.
They get shifted over midway across the table. What a mess!

The IT folks also decided to change the bottom of one of the tables
to have four informational lines (such as totals) at the end
instead of the former two. Again, I have to change my macros to
adjust. But the other file of the pair I use still has two info
lines at the end.

Dallman

----------------
Dallman Ross wrote:

In , Dallman Ross <dman@localhost.
spake thusly:

My problem is, the entry M/dd/yy in the CSV file seems to
Excel to be a non-date string. I have formatted the column
for dates -- that makes no difference.

Oh, brother; I see. The jerks inserted a blank space in front now
in all the columns in the CSV file. I'm not sure yet what the best
way to handle this is -- I'm not wanting to massage the data I
download and import.

Dallman

In , Dallman Ross <dman@localhost.
spake thusly:

Oh, brother; I see. The jerks inserted a blank space in front
now in all the columns in the CSV file. I'm not sure yet what
the best way to handle this is -- I'm not wanting to massage the
data I download and import.

It's taking a bit of work, but now I've mostly fixed things via
a new data query and the TRIM function. I still think it was
stupid of the broker's people to suddenly insert leading spaces
inside CSV fields. This is right out of the file by way of example:

Description,Quantity,Date Acquired,Purchase Price,...
" ADVANCED MICRO DEVICES INC"," 90.0000"," 6/08/06"," 27.42",...

Dallman

You may want to speak with them and tell them not to fix it. If they notice it,
then they might think that it's a problem that should be corrected and then
you'll be distressed again.

Dallman Ross wrote:

In , Dallman Ross <dman@localhost.
spake thusly:

Oh, brother; I see. The jerks inserted a blank space in front
now in all the columns in the CSV file. I'm not sure yet what
the best way to handle this is -- I'm not wanting to massage the
data I download and import.

It's taking a bit of work, but now I've mostly fixed things via
a new data query and the TRIM function. I still think it was
stupid of the broker's people to suddenly insert leading spaces
inside CSV fields. This is right out of the file by way of example:

Description,Quantity,Date Acquired,Purchase Price,...
" ADVANCED MICRO DEVICES INC"," 90.0000"," 6/08/06"," 27.42",...

Dallman

--

Dave Peterson