Menu
Ask a question Report

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

- Sep 29, 2009 at 09:07 AM - Latest reply:  Roprince
- May 18, 2018 at 12:39 PM
Excel Ask a question Report
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 

42 replies

Best answer
Report Respond to 24
- Updated by Jeff on 24/03/17 at 11:48 AM
516
Thank you
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

Thank you, 24 516

Something to say? Add comment

CCM has helped 1649 users this month

View all 22 comments
Report Respond to Sam
Sam - Dec 27, 2017 at 01:32 PM
Thanks very much - works perfectly for large data sets.
Report Respond to teddy
teddy - Jan 27, 2018 at 03:19 AM
it worked.... :) thanku....
Report Respond to SS
SS - Mar 6, 2018 at 08:32 AM
Thank you, this is a simple yet effective solution.
Report Respond to Thaneesh
Thaneesh - Apr 12, 2018 at 09:00 AM
Thank u very much:)
Report Respond to Roprince
Roprince - May 18, 2018 at 12:39 PM
Thank you so much
Report Respond to Justagal
Posts
2
Registration date
Thursday October 1, 2009
Last seen
October 2, 2009
 - Oct 2, 2009 at 12:39 AM
49
Thank you
Actually I find the problem:
Go to:
Registry
HKEY_CURRENT_USER
Control Panel
International
Change the sShortDate
Report Respond to jack
- Jan 13, 2010 at 12:49 AM
31
Thank you
Hi,

this still doesnt help I tried. please help. or am I doing it wrong?
View all 8 comments
Report
holly - Mar 24, 2010 at 04:23 PM
Worked for me too. thanks
Report
miked - Mar 25, 2010 at 09:00 AM
Thanks for the help ... spent too much time trying to figure this one out.
Report
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!
Report
KIDLARK - Apr 5, 2010 at 05:09 PM
Text to columns worked for me too! (I had already formatted the cells first)
Report
mossy - May 6, 2010 at 02:28 AM
Gold star Nazie
Report Respond to venkat1926
Posts
1865
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 - Sep 29, 2009 at 10:32 PM
17
Thank you
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
Report
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!
Report
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.
Report Respond to Mak
- Jan 18, 2010 at 11:09 AM
12
Thank you
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
Report Respond to venkat1926
Posts
1865
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 - Oct 2, 2009 at 12:24 AM
9
Thank you
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.
Report Respond to venkat1926
Posts
1865
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 - Oct 2, 2009 at 03:45 AM
6
Thank you
Thanks for the input
Report
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.
Report
Mike > BooBoo - 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.
Report Respond to venkat1926
Posts
1865
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 - Oct 2, 2009 at 12:39 AM
3
Thank you
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
Ask a question