Hi Ron,

I tried the Data/Text-to-Columns wizard, it works but need to be in the
worksheet. It still help when change in range. Thanks a lot.

Further, macropod above suggested a VBA that also work well too.

Thanks everyone for all the help.

Learn-more


"Ron Rosenfeld" wrote:

On Tue, 12 Aug 2008 20:25:11 -0700, Learn-more
wrote:

Dear All,

I have a worksheet with some date in it like '21/09/2008 or some like
21/09/2008 without the '.

I tried to format on the worksheet or VBA to the date format dd-mmm-yy. Even
when I check the format setting, it shows dd-mmm-yy but the display still
21/09/2008.

Further I tried control-H to find and replace the ' , but nothing found.

If I do it one by one, I can just use F2 and then just hit return, then the
format works ok to dd-mmm-yy.

I also tried copy to other location and paste it back etc, but still not
working.

Any idea why it is like that and not response to my format setting? Any way
in worksheet or VBA to change it and no need to do it one by one?

Any one had any suggestion?

Thanks a lot.
Learn-more


Others have explained the "why".
One other method to change this is to use the Data/Text-to-Columns wizard

Select the relevant cells. They need to be in the same column.

Then Data/Text to columns
Fixed Width
<Next
Make sure there are no line breaks shown
<next
Date: DMY
<Finish

and that should convert them to Excel recognized dates, to which your format
should apply.

--ron