Excel date format won't change [Solved/Closed]

- - Latest reply:  Peaches - Oct 19, 2012 at 09:47 AM
If I enter a date into a cell in my Excel spreadsheet using the shortcut (CTRL+;), I can no longer change the formatting (right-click, format cell). The date format stays as entered... eg. 4/14/2010.

But if I manually enter the date, I can change the format, as long as it isn't entered in the format in the example. Just find this a bit frustrating...

Any help with this issue?
See more 

4 replies

Best answer
37
Thank you
Karl's solution is the one to go with!

Say "Thank you" 37

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

CCM 4510 users have said thank you to us this month

Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
915
23
Thank you
The cell might be pre-formatted to be a text. By pre-format I mean before you enter CTRL+;, the cell was formatted to be text
12
Thank you
OK, here is a sample worksheet I just created. I preformatted Column 1 for dates (e.g. 2010-03-12).

I entered two dates. In the top cell (A8), I can change format, but the lower cell where the date has been entered with slashes cannot be changed.

When I right-click and Format the cell (for this cell), even when I choose a different date format, it will not change in the Sample above the Types for the format...

http://www.speedyshare.com/files/21940807/sample-date.xlsx
this doesnt work for me and I am having the same issue, its like the column which contains an export of data with both types of US and Uk dates in various formats doesnt listen to thing I want to do.

i tried to create new column, define the format in different formats and copy the values, nothing happens.

i tried a formula the attempt to convert it to text and one that attempts to convert it to date formats, nothing happens.

its literally just keeping the same format, data,layout, whatever I do :s

any ideas on this one?
rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
915 -
Karl - Oct 20, 2010 2:30am BST
I have had this problem for a long time and the only solution I have found is this.
1) (Save As) a Tab Deliminated.txt file.
2) Open New Excel
3) Open File and choose Tab Deliminated Option.
4) Go to each Date column and select it and chose Date DMY, MDY(Whichever) format.
5) Select Finish.
All dates should now format ok.

Karl dude... just googled to solve this problem and used your solution... worked perfectly. Cheers!
Worked for me too! Thanks so much!!!
Wonderful. It worked. Seems to be the only why to handle the problem. thanks.
2
Thank you
Nope, all the cells are preformatted to be date (e.g. 2010-04-14). If I enter a date manually in this format (or some other format) it's OK, but if I enter a date using the slashes (e.g. 4/14/2010), then I can't change the formatting later. Doesn't matter what date format I choose, it won't change. I have to use Text to Columns wizard to be able (eventually) to change the formatting on the cell.
rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
915 -
Could you put possibly put a sample file with the issue at some shared location like http://www.speedyshare.com/ and post back the link. In my test, only time I was not able to change the format was when the target cell was pre-formatted to be a text