Changing text to date format

I don't know if I should have started a new thread for this question, but in fact for some reason I can't get any response by clicking on 'New' anyway, so I don't have much choice.

I have a sheet with 1600 dates in the form, for example '1-1-1997' and I must change them to ISO format '1997-01-01'. At present, the cells containing these dates are formatted as 'General', becuase they were imported (by someone else, in another country) from a database.

I tried adding three columns to the right and then 'text to columns' but that produced weird effects, including corruption of existing columns to the right of the three new ones.

In view of the number of conversions needed, any solution need not be of the utmost simplicity, but it must work reliably, becuase checking all 1600 for correct conversion would be tedious and maybe inaccurate.
--
Regards JMW


"Ron B" wrote:

Hello,
I have imported a text file to an excel sheet and the date fields have been formated as text. I have not been able to convert the cells back to date format. When I do, the data turns into ###########.

Any ideas as to why this is happening and what I can do to change the data to date?

I think you have to decide what's really in those cells first.

If the values were entered as text, then changing the format of the cells to
General will still leave the value as text. But with the next change (even just
F2|Enter), excel will see it as a date.

So the first thing I'd do is try to reenter those values so that excel could
interpret them as dates.

Ctrl-a (twice in xl2003) to select all the cells.
edit|replace
what: - (hyphen)
with: - (hyphen)
replace all.

Depending on your computer's date format and the values in the cells, you may be
almost done.

I'd format a few of the cells using an unambiguous date format: dd-mmm-yyyy
(for example).

Then you can see if values got converted to dates correctly.

If they did, format them the way you want (yyyy-mm-dd).

But if it tried to convert 12-7-1997 into December 7, 1997 and you wanted July
12, 1997, you have more work to do.

One way that you could try is to convert your pc's short date settings to the
format that you want.

close excel
windows start button|settings|control panel|regional settings applet
Date Tab (fix that short date setting)
(I use win98, so your steps might be slightly different.)

Then back to excel and try it out again.

If it worked ok, then close excel and change your regional settings back to the
way you like them. (I didn't actually have to close excel, but it couldn't
hurt!)


==============
Another way if your data is laid out nicely in columns is to select a column
(it's one column at a time).
Then data|text to columns
choose delimited (uncheck everything)
tell excel that the field should be mdy or dmy
This is the "format" of the current values--not what you want.
and finish up by putting it in the same location.
(You can change the format to what you really want later.)

Then do the next column, and the next, and the next....


J M Woodgate wrote:

I don't know if I should have started a new thread for this question, but in fact for some reason I can't get any response by clicking on 'New' anyway, so I don't have much choice.

I have a sheet with 1600 dates in the form, for example '1-1-1997' and I must change them to ISO format '1997-01-01'. At present, the cells containing these dates are formatted as 'General', becuase they were imported (by someone else, in another country) from a database.

I tried adding three columns to the right and then 'text to columns' but that produced weird effects, including corruption of existing columns to the right of the three new ones.

In view of the number of conversions needed, any solution need not be of the utmost simplicity, but it must work reliably, becuase checking all 1600 for correct conversion would be tedious and maybe inaccurate.
--
Regards JMW

"Ron B" wrote:

Hello,
I have imported a text file to an excel sheet and the date fields have been formated as text. I have not been able to convert the cells back to date format. When I do, the data turns into ###########.

Any ideas as to why this is happening and what I can do to change the data to date?

--

Dave Peterson

You could download the third party tool: AddinTools Assist from
http://www.addintools.com. It includes several functions. One of these
functions is convert data of cells to text/number/date-time type.
It can convert data of all formats to text type.
It can recognize these formats as following and convert to date-time type:
2003.1.2, 2003.1.2 11:12:13:14PM, 2003-1-2, 2003-1-2 11:12:13.14PM,
20030102, 20030102 11:12:13:14PM, and locale format on your computer, and so
on …