Report

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

Ask a question rznbyz - Last answered on Sep 1, 2017 at 04:34 AM by vidya
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
Helpful
+482
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
Satti- May 19, 2017 at 06:29 AM
Super Trick..Thank You
Reply
Mtg- Jun 21, 2017 at 11:03 AM
Wow! I struggled with this for so long. Can't stand date/format issues.
Reply
Mtg- Jun 21, 2017 at 11:03 AM
Sad part is, we should not need a "trick" for this. Thank you - it did work for me no issues.
Reply
saradhi- Jul 22, 2017 at 11:26 AM
Thanks, It worked for me
Reply
vidya- Sep 1, 2017 at 04:34 AM
thank you so much , works well !
Reply
Helpful
+47
plus moins
Actually I find the problem:
Go to:
Registry
HKEY_CURRENT_USER
Control Panel
International
Change the sShortDate
Helpful
+29
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!