Annoying Format Cells Problem HELP!

[Jon Hiller]

Hello:

I'm running into a continually annoying issue when trying to edit
the cell formats......The data is in the cells is imported as tab delim
text, and the literal data is yyyy-mm-dd hh:mm:ss in original
format. When I try to change the format of the cells to simply mm-
dd-yy, it does nothing. However if I click on the cell as if to edit
after setting the format to mm-dd-yyyy, then exit the cell, it
changes to what I've set....WTF!!!! I can't believe I have to click on
4,000 cells to format the date properly. I'm using Excel on a Mac
with OSX and also tried my Windows XP Excel with the same
results..... Any help would be greatly appreciated.

Jon Hiller
(e-mail address removed)

 

[Dave Peterson]

Number format applies to numbers. So excel must think that your data is not
numeric (dates/times are numbers, too).

One way to convert your text dates/times to number dates/times is to:
select an empty cell
copy it
select your range of dates/times (whole column(s)??)

Edit|Paste special|check Add

Your data will look like:
37822.50838

Now apply that nice date format.

 

[Peo Sjoblom]

Another way would be to select the column, do data>text to columns,
skip to step3 and select date and YMD from the dropdown. Given that the
data is imported the OP might want
to do that directly when importing instead.