Date-formatting problem

[Ulodesk]

I noticed that in a worksheet that I have been keeping for years, many of the dates in one column, entered as, e.g., 4/15/01, now show as a five-digit number, such as 40987. Interestingly, this has not happened on another, similar sheet in the same workbook.

Checking the cell formatting, the problem cells appear to have been formatted as a non-asterisked date format of the type indicated above, so, as I understand it, they shouldn't be influenced by other changes. I found one that had an asterisked date format and changed it to non-asterisked, but nothing happened.

I was guessing that perhaps daylight savings time on the computer might have caused a problem, but I don't recall this happening in the past, and anyway the date format has no time in it.

Can I restore these? They are purchase dates for various items and while it's possible I have them in a saved backup, I don't know how long the problem has existed.

[Pecoflyer]

What is an "asterisked date format"?

EDIT on't bother I've got it

[BobBridges]

I don't have it; what is an "asterisked date"?

Also, Ulodesk, did you get your problem solved? If not, can you post the workbook so we can take a look at it?

[Ulodesk]

Sorry for delay; I was not available for a while.
I am attaching an example of rows from the spreadsheet I mentioned. All I did was delete sensitive info from other columns (not the date one) and also two other worksheets, which don't involve dates.

Thanks for checking back; I hope you figure it out.

[BobBridges]

Ok, this is very odd. I need some input from others here. The OP has some date fields that Excel knows are dates, but that it insists on displaying in General format even after I tell it to use some other format. Observe the attached screen shot:

1) In the formula bar at the top is "2006-5-31". This is how I know that Excel knows it's a date.

2) The selected cell, C5, says "38868"; Excel is displaying the date in General Format.

3) In the Format-Cells dialogue at the right is the format that I set for that cell. I saved that setting and the date's format did not change from General. Then I entered the dialogue again, and it's still set to dd-mmmm-yy; Excel didn't change the format to anything else, it's simply ignoring it.

[BobBridges]

Got it! Ulodesk, I stumbled across this purely by accident, looking around for some setting that might be doing this to you, and at first when I found the right one I didn't even notice it had fixed the problem. Do this:

1) On the ribbon, go to Formulas.

2) In the fourth ribbon section, entitled "Formula Auditing", notice that option H, "Show Formulas", is highlighted.

3) Turn it back off.

When I mouseover that option, it says it's to "Display the formula in each cell instead of the resulting value". It also says the keyboard shortcut for this option is <Ctrl-`>; I expect you hit that keystroke accidentally without knowing what you did. At least, I know that several times I've accidentally hit keystroke sequences that did something unexpected, unwanted and ununderstood.

I've never noticed that Formula-Auditing section before; now that I'm looking, it seems to have some useful features. In fact, the only thing I've ever used in the whole Formulas bar is the Name Manager. Thanks for your question, Ulodesk; I foresee a fruitful exploration coming up.

[Ulodesk]

BobBridges, bravo! I use Excel in such a limited way, I don't imagine I ever would have found it. You're undoubted right about the keystroke; I use as many keyboard shortcuts as I can remember, and I probably was trying to put an acute accent over an e somewhere.

Many thanks.