Date input and format

[jmhultin]

This seems so basic, I'm embarrassed to ask. Normally, when I enter a date, such as "9/24/12", Excel recognizes it as such, and displays and calculates it as a date. I've inherited a workbook from somebody else, and when I enter "9/24/12", the formula bar reads "=9/24/12", and the result is a very small decimal number. Formatting the cell as a date only displays "1/0/1900", the date value of that small decimal value. The only way seems to be to enter the date as text, or "=DATE(2012,9,24)", but that's a pain. Another cell seems to insist upon a formula, even when text is entered.

Gotta be a simple solution.

Thanks

[Sean Thomas]

can you post the workbook?

[jmhultin]

The highlighted cells are the issue. If I enter the "street" beginning with a number, it gives me an error message if it's not a valid formula. The other highlighted cells are dates.

[Kevin UK]

Hi jmhultin

I don't know what Excel version you are using, in 2010. Go to File > Options > Advanced > Scroll all the way down > And un-check both boxes in the Lotus compatibility settings for:

Transition formula evaluation & Transition formula entry. Click OK.

[jmhultin]

It works! Thanks!