Excel date format won't change

Solved/Closed
gto49 - Apr 14, 2010 at 10:06 AM
 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?
Related:

4 replies

Karl's solution is the one to go with!
39
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Apr 14, 2010 at 10:44 AM
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
23
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...

https://authentification.site/files/21940807/sample-date.xlsx
12
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Apr 14, 2010 at 12:38 PM
Quite strange. The lower cell is definitely not date but is text. If you expand that column, you will see that it will remains on left side. The dates are by default, right justified as the one above it. I entered one value down with CTRL + ; and was still able to format it. Since you said you have checked that the cell was pre-formatted to be date and only then you entered the short cut, I guess some thing is forcing the date entered via that short cut to appear as text
0
OK, has it anything to do with any option settings in Excel? Even if I try to reformat the cell using any other format, it just won't change...
0
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.
0
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?
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Jun 7, 2011 at 01:03 AM
0
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.
3
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Apr 14, 2010 at 11:50 AM
Could you put possibly put a sample file with the issue at some shared location like https://authentification.site 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
0