Ask a question Report

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

gto49 - Latest answer on Oct 19, 2012 09:47AM

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?

Answer

rizvisa1 4230Posts Thursday January 28, 2010Registration date ContributorStatus May 7, 2013Last seen Apr 14, 2010 10:44AM

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

Answer

jgussman0724 Oct 3, 2012 12:03PM

Karl's solution is the one to go with!

Answer

gto49 Apr 14, 2010 11:32AM

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 4230Posts Thursday January 28, 2010Registration date ContributorStatus May 7, 2013Last seen - Apr 14, 2010 11:50AM

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

Answer

gto49 Apr 14, 2010 12:10PM

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

rizvisa1 4230Posts Thursday January 28, 2010Registration date ContributorStatus May 7, 2013Last seen - Apr 14, 2010 12:38PM

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

gto49 - Apr 14, 2010 01:14PM

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...

Karl - Oct 19, 2010 09:30PM

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.

George - Jun 6, 2011 10:04PM

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 4230Posts Thursday January 28, 2010Registration date ContributorStatus May 7, 2013Last seen - Jun 7, 2011 01:03AM

See if this helps u
http://en.kioskea.net/forum/affich-595248-text-format-to-date-month-year-on-excel#11

Bish - Aug 14, 2012 04:04AM

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!

Sammy - Oct 5, 2012 12:13PM

Worked for me too! Thanks so much!!!

Peaches - Oct 19, 2012 09:47AM

Wonderful. It worked. Seems to be the only why to handle the problem. thanks.

Ask a question

Not a member yet?

sign-up, it takes less than a minute and it's free!

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.

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

Not a member yet? sign-up, it takes less than a minute and it's free!

Not a member yet?

sign-up, it takes less than a minute and it's free!