Entering a date results in a decimal fraction

[Bobo Link]

In a worksheet I created several years ago, when you key enter a date
in the format of m/d, m/d/yy, or m/d/yyyy the result is a formula of
=m/d (and likewise =m/d/yy, =m/d/yyyy) a calculation of a decimal
fraction and the display of January 0, 1900, since the cell is
formatted as a date.

Opening a blank spreadsheet under the same preferences, exhibits
normal behavior--that is treating the entry as a date and formatting
it as a date.

Likewise in this particular worksheet, entering 2+3 (without the
usually required equal sign) results in =2+3 and displays 5. Normal
behavior would display text 2+3.

I've looked at cell formatting, data validation, Tools>Options, etc.
without resolving this. The worksheet contains formatting, validation,
and VBA macros, but no attempt to change the.

I'd like to enter dates without the trouble of typing the formula
=Date(yyyy,mm,dd).

Actually it would be nice to know how turn this 'switch' on and off,
as I've always wondered why with all the 'features' in Excel it
required you to use the = sign for obvious calculations.

 

[Niek Otten]

Tools>Options>Transition tab, uncheck Transition formula entry (and possibly others)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| In a worksheet I created several years ago, when you key enter a date
| in the format of m/d, m/d/yy, or m/d/yyyy the result is a formula of
| =m/d (and likewise =m/d/yy, =m/d/yyyy) a calculation of a decimal
| fraction and the display of January 0, 1900, since the cell is
| formatted as a date.
|
| Opening a blank spreadsheet under the same preferences, exhibits
| normal behavior--that is treating the entry as a date and formatting
| it as a date.
|
| Likewise in this particular worksheet, entering 2+3 (without the
| usually required equal sign) results in =2+3 and displays 5. Normal
| behavior would display text 2+3.
|
| I've looked at cell formatting, data validation, Tools>Options, etc.
| without resolving this. The worksheet contains formatting, validation,
| and VBA macros, but no attempt to change the.
|
| I'd like to enter dates without the trouble of typing the formula
| =Date(yyyy,mm,dd).
|
| Actually it would be nice to know how turn this 'switch' on and off,
| as I've always wondered why with all the 'features' in Excel it
| required you to use the = sign for obvious calculations.

 

[Bobo Link]

Tools>Options>Transition tab, uncheck Transition formula entry (and possibly others)

That did it thanks Niek Otten. I have a feeling this persisted
because the original was built in Lotus 1-2-3. Guess that dates me...