Dear experts,

I've a worksheet of which column D & E are for dates. However, in these 2
columns, the users input the dates in different format. I want to change the
dates into one format only such as: 21-Feb-09 (dd-mmm-yy). However, I
highlighed the cells and changed the format to "dd-mmm-yy", the original
dates do not change and until I re-type the dates again. It's a headache
exercise if I need to re-type the dates again, please advise any quick method
to achieve my goal.

Thanks in advance

Place the cursor in the date cells and press F2 and give enter. For
converting bunch of cells and columns instead of pressing F2 and Enter
continuously it can be converted using Delimit. But without seeing the data
its difficult to suggest the option.

If the above suggestion is not useful to you then post your data how the
dates are entered in your excel.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Freshman" wrote:

Dear experts,

I've a worksheet of which column D & E are for dates. However, in these 2
columns, the users input the dates in different format. I want to change the
dates into one format only such as: 21-Feb-09 (dd-mmm-yy). However, I
highlighed the cells and changed the format to "dd-mmm-yy", the original
dates do not change and until I re-type the dates again. It's a headache
exercise if I need to re-type the dates again, please advise any quick method
to achieve my goal.

Thanks in advance

It sounds like the dates were entered or imported as text. If they are you
could temporarily enter a new column to the right and use:

=datevalue(D2)

That would convert the text date to a value date. Format it however you
wish.

Then copy, paste special, values back over column D. lastly remove the new
temporary column D.

I hope that helps.

Frank


"Freshman" wrote:

Dear experts,

I've a worksheet of which column D & E are for dates. However, in these 2
columns, the users input the dates in different format. I want to change the
dates into one format only such as: 21-Feb-09 (dd-mmm-yy). However, I
highlighed the cells and changed the format to "dd-mmm-yy", the original
dates do not change and until I re-type the dates again. It's a headache
exercise if I need to re-type the dates again, please advise any quick method
to achieve my goal.

Thanks in advance

Hi Frankwood,

Thanks for your tips. However, it didn't work. I don't know how the users
entered the dates. Their dates are mostly, for example - 25 February, 2004. I
checked the cell format and it was a date format like 01-Mar-05. Apparently,
it looks OK but I don't know why your tip didn't work. Anyway, I used a more
complicated method to solve this problem and it was done. Thanks anyway.

Best regards.

"FrankWood" wrote:

It sounds like the dates were entered or imported as text. If they are you
could temporarily enter a new column to the right and use:

=datevalue(D2)

That would convert the text date to a value date. Format it however you
wish.

Then copy, paste special, values back over column D. lastly remove the new
temporary column D.

I hope that helps.

Frank


"Freshman" wrote:

Dear experts,

I've a worksheet of which column D & E are for dates. However, in these 2
columns, the users input the dates in different format. I want to change the
dates into one format only such as: 21-Feb-09 (dd-mmm-yy). However, I
highlighed the cells and changed the format to "dd-mmm-yy", the original
dates do not change and until I re-type the dates again. It's a headache
exercise if I need to re-type the dates again, please advise any quick method
to achieve my goal.

Thanks in advance