Irregular Date Problem

[Guest]

The following problem only occurs in certain tabs in only one of my
spreadsheets.

When I enter a date -any date- it returns "1/0/1900." I have entered
various dates and formatted the cells as numbers, and the cells return
different numbers for the differing dates; however, when I reformat them as
"dates" they all return "1/0/1900" or some function of this date whether it
be "1/1900" or "1/0."

I can enter a date into another tab -- in which the dates are working --
then copy and paste the date into a tab -- in which the dates aren't working
-- and then the correct date appears. However, if I modify that cell to be
any other date, it returns to the "1/0/1900."

The peculiar things is this problem lies only in certain tabs; roughly 15
out of thirty. Also, whenever I create a new tab in the particular
spreadsheet the new tabs work fine.

This is the first time I haven't been able to figure a problem out in excel.
Any help or direction would be much appreciated.

 

[Dave Peterson]

There is a worksheet setting that you have to turn off.

In xl2003:
Tools|Options|Transition tab|uncheck transition formula entry.

When that setting is turned on, excel acts like lotus 123.

And when you type: 07/27/2007, it sees it as 7 divided by 27 divided by 2007. A
very small number. And that small number formatted as a date is 01/00/1900.

I'd uncheck all those Lotus 123 transition settings if I were you.

 

[Guest]

Your suggestion fixed my problem perfectly. Thanks so much for your help I
appreciate it very much.