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:
">On Thu, 27 Nov 2003 22:43:11 -0600, Rajkumar
>>Frequently i am downloading some datas from our ERP,in which the date's
>>are downloaded as in this style.
>>i am not sure this in what format.But when i use the formulae
>>=days360(a1,b1,true) to find the difference between 2 dates,excel is
>>giving error msg as #VALUE!
>>pls help me to sort out this issue.
>I don't know what your regional settings are, but the following should work
>1. Select the column in which the dates are listed.
>2. Data/Text to Columns
>5. Column Data Format: Date: DMY
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. :oD