Excel is converting numbers to dates

[jeffery_frick]

I am using Excel 2007 and it keeps converting my numbers into dates. For example, it will display the number 2011 as 3-Jul-05. I have changed the formatting of the cell (both to text and to number format) and saved it. But eventually, when I go back to the spreadsheet it has converted these back to dates again. Is there a global setting on Excel that I am missing?

 

[pagrender]

I had a similar issue at one time - but it might not apply to you so let's test something.

Find one of the cells that has the date in it. Right click on it and choose Format Cells. Choose Custom. What is the format in that cell?

My issue was there was a weird format and when I deleted that format, everything was fine again. Before you delete any formats, be sure to save your file so you can revert to it if necessary.

 

[jeffery_frick]

The format of the cell is selected as date when I go to it, even though I want it to be a number or text. When I click custom, the format that is highlighted is:

[$-409]d-mmm-yy;@


If I attempt to delete this format it says:
You cannot use this command on a protected sheet. To use this command, you must first unprotect the sheet.

However, neither the sheet nor the workbook is protected.

 

[pagrender]

I guess the issue I had was a little different than yours. I don't know why you're sheet is "protected" when in actuality it is not.

Sorry I'm not any help.

 

[DwinZly]

I'm having the same issue with my sheet. It will be fine for a while and all of a sudden I will open it one day and find that all of my cells with numbers in them have been formatted to be a date. It's really obnoxious to have to go back and change them all. I really hope someone knows the answer to this.

 

[jeffery_frick]

Pagrenders solution worked on my sheet so far. In my previous post I indicated that my sheet was not locked. In actuality, a completely different sheet in the workbook was locked. When I unlocked that sheet, I was able to delete the unusual custom format referenced in the previous post. So far this has helped. If it crops up again I will repost for more help.

 

[DwinZly]

That was one of the strangest things I've ever seen Excel do. I found the weird formats, erased them, and all of my dates converted back to their usual form. It looked like there was a cell reference within the format. Anyway, thanks for the input!

 

[pagrender]

Glad the solution is working for you. Let us know if you need anymore help.

Thanks for the feedback!