Dear All,

I have a column in this Excel defined as Date format, "dd-MMM-yy"
type.
However, when I open the file, some cells have value like
"20/11/2005".
After I double-clicked the cell, it would change to "20-Nov-05".

Anyone could advise what "double click" triggered in this case? How
to
change all the values in the column simultaneously?

Thanks.


--
qqxz
------------------------------------------------------------------------
qqxz's Profile: http://www.excelforum.com/member.php...o&userid=30773
View this thread: http://www.excelforum.com/showthread...hreadid=504421

Hi ....., (use your name)
Some questions on Date and Time might be answered in
http://www.mvps.org/dmcritchie/excel/datetime.htm

You have to be a bit careful, with date inputs especially between
d-m-y and m-d-y formats perhaps you should test with
=TEXT(A1, "dd.MMM.yyyy") to see that you have what you
thing you have before continuing.

I you already had a number that Excel recognized as a date.
Excel keep track of your date as y-m-d, m-d-y, or d-m-y
(that's the order that you Excel thinks you display your short date),
then changing the format to a different number format would be
done instantly when you change the number format.

If Excel recognizes your data as a date upon input. You
can change number formats anytime and if Excel recognized
your data as a number before changing the format the change
will be immediate upon changing the format. If your data is
text Excel will not recognize the change of format to number
until you reenter the number.

The double click followed by Enter is a reentry.
Use of F2 then Enter is a reentry, a bit faster for you.

Personally I use yyyy-mm-dd to avoid problems a lot of
problems. I always disliked two digit years on permanent
records (not to mention one digit years).

I would avoid using my TrimALL macro on date data, if there
is any question of format. But you can easily reenter your data
which I presume is currently text =ISTEXT(A1)

Select an empty cell (no content, no spaces, no formulas
to use cell M1 it must test True for =ISBLANK(M1)
Copy that cell, then
Select the range you want to fix, can be the entire column(s)
Edit, Paste Special, Add
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"qqxz" wrote in message
...

Dear All,

I have a column in this Excel defined as Date format, "dd-MMM-yy"
type.
However, when I open the file, some cells have value like
"20/11/2005".
After I double-clicked the cell, it would change to "20-Nov-05".

Anyone could advise what "double click" triggered in this case? How
to
change all the values in the column simultaneously?

Thanks.


--
qqxz
------------------------------------------------------------------------
qqxz's Profile: http://www.excelforum.com/member.php...o&userid=30773
View this thread: http://www.excelforum.com/showthread...hreadid=504421