Converting dates to numbers

[hogesta]

Hi everyone,

First time poster here, appreciate any and all help.

I have an excel document that continues to convert dates to numbers, and I have no idea why. For example, 2/2/14 changes automatically to 41672 and 4/4/14 changes automatically to 41733 (left adjusted in the cell).

The cells are all formatted as dates (in the format of mm/dd/yy), and there are no formulas associated with these cells. I've tried copying and pasting these cells into a new document, and all dates work as they should. Unfortunately, simply creating a new document is not an option at this time.

Has anyone seen this happen before? Thanks in advance from a frustrated user!

 

[Michael M]

The cells look they are formatted as "general" or "Text", hence the numbers !
If you right click on one of the cells and select Format cells, what is highlighted in the dialog box that appears.
Also are there any macros being enabled for that particular sheet ?

 

[hogesta]

Thanks for the reply, Michael.

They are not formatted as General or Text. They are formatted as Date, I've checked this a number of times and also tried going in and modifying it/changing back to date (I even tried making it custom a few times). Still no dice. I also have no macros enabled on my sheet. Weird, right?

Any other ideas?

 

[barry houdini]

Hello hogesta, welcome to MrExcel

This will happen if you have the "show formulas" option enabled (I know you don't have formulas for the dates, but it's a "side effect" of that option)

You can toggle on/off with shortcut combination CTRL+`

or go to File > Options > Advanced > under "Display options for this worksheet" untick "show formulas in cells instead of their calculated results"

 

[Michael M]

Hey Barry
I didn't know that....a great little learner...I'm not going to press "like"....but I like that..well done..

 

[hogesta]

Hi folks-

Thanks for all the replies. I really appreciate it. Houdini, I wasn't able to follow the directions you provided (I'm on a Mac, which could be why), but it did prompt me to a few other areas that helped me solve the problem. Thought I'd outline them below in case anyone else stumbles across this looking for answers. Again, I'm working off of a Mac with the most recent version of Excel.

-Highlight the affected cells
-Go to the "formulas" tab
-Click on the "show" icon (it is a drop down icon with a magnifying glass)
-Make sure "show formulas" is unchecked

Thanks again, everyone!