Previous Topic Next Topic

Unable to change date format in Excel

Solved/Closed
Report
-
 Tobby -
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
Related:

5 replies

Answer 1 / 5

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
535
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

View all 24 comments
Report

Thank you, this is a simple yet effective solution.
Report

Thank u very much:)
Report

Thank you so much
Report

you are the best
Report
> Amooon
Or :
- select area
- then "search & replace" "-" by "-"
Answer 2 / 5

Hi Jack,
I can help you with this. Select the column/row that you need to reformat.
Go to the Data Tab
From the Data tab, Select Text to Columns
This will open a Wizard called "Covert Text to Columns"
You'll see 2 choices for your original data type: Delimited or Fixed Width
If your current date format has commas, tabs, dashes, etc., then click Delimited.
If your current date format has only spaces separating everything but no characters, then click Fixed.
Click NEXT once you selected your origiinal data type
The next screen, you can skip by clicking NEXT again
Now you'll see a screen that lets you slect the data format that you want
Select the DATE button
Then, from the drop down, select the new date format you want to use.
Click Finish
This should do the trick. I hope this helps!!
View all 8 comments
Report
> jack
Not sure if any of these more complicated solutions work, but I saved it as a csv and it worked perfectly. Thanks!
Report
> jack
Text to columns worked for me too! (I had already formatted the cells first)
Report
> jack
Gold star Nazie
Report

@Nazie

Thanks Nazie... It worked for me..
Report

Very 2 thanks for the same I also faced the same problem but resolved by your solution in this blog .
Answer 3 / 5
Posts
2
Registration date
Thursday October 1, 2009
Status
Member
Last seen
October 2, 2009
Actually I find the problem:
Go to:
Registry
HKEY_CURRENT_USER
Control Panel
International
Change the sShortDate
Answer 4 / 5
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 803
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

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

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.
Answer 5 / 5

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
Ask a question