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

gto49 - Apr 14, 2010 at 10:06 AM - 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 

13 replies

Best answer
jgussman0724 - Oct 3, 2012 at 12:03 PM
31
Thank you
Karl's solution is the one to go with!

Thank you, jgussman0724 31

Something to say? Add comment

CCM has helped 1970 users this month

rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Apr 14, 2010 at 10:44 AM
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 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 7, 2011 at 01:03 AM
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 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Apr 14, 2010 at 11:50 AM
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