How do I convert the date format into a different style?

[CrystalHR]

Hi -

I have a date listed as 1/21/09 and I need it to read as Jan-21-09 for a calculation purpose. I have already highlighted the group of dates and right clicked the mouse to select format and selected date - and then chosen the option of Jan-21-09. However, nothing is changing - it doesnt not seem to be recognizing the request.

There are about 700 dates that need to be converted- and without doing it manually - im wondering if anyone is aware of some sort of restriction that may be on the sheet that I can remove - In order to allow me to change the dates into this format?

Any feedback would be appreciated!

Thanks
Crystal

 

[Norie]

dbrown

I don't think that's the problem.

I'm in Europe - I can format a date on a worksheet anyway I like, it's the value that's in the cell that's important not the format.

 

[dbrown]

Norie,

I am not profering he change the format. Using text to column physically changes 21/12/2009 to 12/21/2009 just as if you typed it in manually. It is not a format change but a physical change. This is a really common problem mostly caused by IT departments who use inaccurate regional settings when installing a computers operating sytem.

Try it out yourself (assuming your default date style is DMY): type out these dates:
12/21/2009
04/13/2010
11/04/2009

These three dates are typed as MDY (not format) your computer will show the first two as a text and the last one will be shown as a date (albeit a wrong date since your computer will recognise it as 11 April 2009 instead of 4 November 2009). How do you solve this problemfor over 700 cells with MDY dates.

The manual solution is to retype the dates like this:
21/12/2009
13/04/2010
04/11/2009
Or simple use the steps for text to column as highligted.

Again this technique is NOT a formatting technique, it depends on how your computers operating sytem recognises dates. It is important to know what you computer is set up as a trick is to hit Ctrl+; (control key + semicolon) and enter, this will give you todays date in your computers default Date style.

To check/change you Computers Date Settings go to:
Control Panel>Regional and Language Options
If you see English (United Kingdom) like in my case then 12/21/2009 will not be recognised as a date but as a text and must be typed in as 21/12/2009.
If the regional settings is English (United States) then all your dates must start with Months.

cheers

 

[CrystalHR]

DBrown - excellent help - that was resourceful.
Thank you to all of you for your feedback.