Correcting date format?

[StargateFan]

I found some data that I needed to add to a spreadsheet that had dates
in a format I don't use, though it's a standard one in Excel of
mm/dd/yyyy. I first saved the data in a text file so even though it
came off the web, the text files converted the data to plain text
format.I searched in the archives and learned this new neat trick
from here to convert the data to a format my regional settings would
supposedly recognize and then display correctly:

http://groups.google.ca/group/micro...4?lnk=gst&q=convert+date-format&rnum=4&hl=en#
">On Thu, 27 Nov 2003 22:43:11 -0600, Rajkumar

I don't know what your regional settings are, but the following should work
regardless:

1. Select the column in which the dates are listed.
2. Data/Text to Columns
3. Next
4. Next
5. Column Data Format: Date: DMY
6. Finish

--ron

The above conversion wizard works great ... when it works. It changed
some dates but not others. I thought eventually that the cause MIGHT
be an extra space anywhere in the dates that didn't get switched so I
did a search-and-replace for that and that corrected maybe two or
three of the remaining ones but that's all. I've gone corrected some
of the remaining dates manually and just can't see where the problem
might lie as to why the conversion didn't take. I initially made the
column a general text column and a date column and nothing seems to
make a difference. Fiddled around with saving to a separate column
and changing cell formats there, but again, nothing.

If this conversion wizard could work 100%, it would be a serious tool
for any future use. But as it only seems to convert about 75% of the
dates at this stage, it's effectiveness is greatly diminished.

Does anyone know what the solution might be to make this work all the
time? Thanks. D

 

[StargateFan]

On Sat, 07 Apr 2007 11:08:41 -0400, StargateFan

[snip]

The above conversion wizard works great ... when it works. It changed
some dates but not others. I thought eventually that the cause MIGHT
be an extra space anywhere in the dates that didn't get switched so I
did a search-and-replace for that and that corrected maybe two or
three of the remaining ones but that's all. I've gone corrected some
of the remaining dates manually and just can't see where the problem
might lie as to why the conversion didn't take. I initially made the
column a general text column and a date column and nothing seems to
make a difference. Fiddled around with saving to a separate column
and changing cell formats there, but again, nothing.

If this conversion wizard could work 100%, it would be a serious tool
for any future use. But as it only seems to convert about 75% of the
dates at this stage, it's effectiveness is greatly diminished.

Does anyone know what the solution might be to make this work all the
time? Thanks. D

I've kept at this and I did find out which dates were converted and
why.

All the date formats of mm/dd/yyyy were converted to my preferred
format if the dd was less than "12". I mangled the dates. In
actuality, XL2K only converted the ones that although are in
mm/dd/yyyy, it interpreted as dd/mm yyyy.

Well, did it again ... after over an hour fighting with this, figure
out that I needed to not choose DD/MM/YYYY in the conversion wizard,
but to choose MM/DD/YYYY despite all the messages in the archives
saying the contrary. So, it depends on the format of the original
date. Must keep the same format as it's presented in. _Then_ after
the conversion I was able to change the display to yyyy.mm.dd.ddd
while the date itself was still to my regional settings of yyyy.mm.dd.

Thanks at any rate. The archives are such a great resource and that
date conversion wizard under the DATA pulldown menu will be something
I use a lot in future! D