Report

Unable to change date format in Excel [Solved/Closed]

Ask a question rznbyz - Last answered on Mar 9, 2017 at 07:49 AM by Augusto
Hello,
I have an excel worksheet with a date column, the format is set to dd-mmm-yyyy but the dates in the columns display as mm/dd/yyyy. If I double click in each individual cell and then move out of that cell the format changes and the dates appear correct. I have 24854 dates I need to change, I can't take time to double click in each cell. Any suggestions? Thanks
See more 
Helpful
+460
plus moins
This works:
  • First change your format to the specific date that you want for your column of data.
  • With the column selected, select "Copy"
  • Open notepad (NOT WORD, it has to be notepad)
  • Paste your data into notepad.
  • In notepad, press CTRL + A (selects all cells) then press CTRL + C (copies data)
  • Paste data back into Excel.
  • It will say something about size not being the same, paste it anyways.


That works.

-24
miley- Dec 6, 2010 at 08:53 AM
I was having the same problem and this solution worked a treat thanks
Michelle- Dec 7, 2010 at 07:00 PM
This is a problem I have had for several months and TEXT to COLUMNS is the answer!! Thank you, thank you!
AJN- Oct 9, 2012 at 04:01 AM
Thank you thank you thank you. Been looking for a cheat/shortcut on this one for a long time.
Golu- Oct 12, 2012 at 04:16 AM
Does not work for me? :(
Augusto- Mar 9, 2017 at 07:49 AM
Hi, It was helpfull!

In my case I need save a new worksheet with modifications.
Reply
Helpful
+46
plus moins
Actually I find the problem:
Go to:
Registry
HKEY_CURRENT_USER
Control Panel
International
Change the sShortDate
Helpful
+28
plus moins
Hi,

this still doesnt help I tried. please help. or am I doing it wrong?
holly- Mar 24, 2010 at 04:23 PM
Worked for me too. thanks
miked- Mar 25, 2010 at 09:00 AM
Thanks for the help ... spent too much time trying to figure this one out.
CJ- Mar 26, 2010 at 01:22 PM
Not sure if any of these more complicated solutions work, but I saved it as a csv and it worked perfectly. Thanks!
KIDLARK- Apr 5, 2010 at 05:09 PM
Text to columns worked for me too! (I had already formatted the cells first)
mossy- May 6, 2010 at 02:28 AM
Gold star Nazie
Helpful
+15
plus moins
how did you format the cell

ok suppose tje relevant date is 1 sept 2009
you enter (in my excel you have to enter as m/d/yy)
9/1/09
now go to format-number-custom
and type at the top window
dd-mm-yyyy
and sees what happens
rznbyz- Sep 30, 2009 at 06:38 AM
Nothing happens....that is the problem. However, if I double click inside one of the date cells, after I have changed the format to the custom format, the date format changes to the correct format. But I can't go through the worksheet and double click in each cell...that would take a week!
Neelu- Oct 29, 2010 at 06:21 AM
i have a date format in mm/dd/yyyy but I want to convert into dd/mm/yyyy in Ms-excel 2003. if any body know then plz tell me.
Helpful
+12
plus moins
the reason for this is as mentioned above - depending on the users settings - i.e. id it's US for example, whateer the user types in the cell will default to the mm/dd/yy (that's according to the default US format)

By clicking into the cell then clicking out, the cell recognises your settings - hence whay it reverts to the UK format

hope that makes sense
Helpful
+9
plus moins
Im guessing. this may something to do with regional settings. Regional settings is control panel. This appears to be operating systems pronblem

place the problem in
PC review forums whose url is

http://www.pcreview.co.uk/forums/

choose the relevant newsgroup for your operating system.
Helpful
+6
plus moins
Thanks for the input
BooBoo- Jan 6, 2010 at 08:46 AM
Why would double clicking the cell make the format change take place, I am still having this problem. In Excel 2003 you could do some tricks with coping and paste special values, but that does not work in Excel 2007.
Mike- Jan 11, 2010 at 06:27 PM
If you save your file as csv and change the format it will work. Its just a jacked up excel file.
Helpful
+4
plus moins
sorry one more guess work

is the cell in which date is entered originally formatted as "text" . please check this

you better format all the cells as "general" and then enter the date and format it and see

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!