Date shows as 01/01/00

[Guest]

We have a spreadsheet that we want to enter a date in a cell. I have changed
the format to Date 03/01/01 format but no matter what I do, any date is
converted to 01/01/00. I have tried inserting a column, copying to another
spreadsheet, etc. I know this is probably simple, but it's driving me crazy.
Help please.

 

[Guest]

In an un-used cell enter:

=TODAY()

if this is not the correct date, then touch CNTRL-ALT-F9 on the keyboard

post what happens.

 

[Dave Peterson]

Try tools|options|transition tab|Uncheck Transition Formula Entry.

With this checked, excel behaves like Lotus 123 and sees a division:

But I think your post is wrong <vbg>.

You see 01/01/00 when you type in a date of 01/03/01.

1 divided by 3 divided by 1
is equal to: 0.333333333333333
Or 1/3 of day one in excel speak.
And day one is January 1, 1900.

(You'd see Jan 1, 1900 8:00 AM if you formatted it differently.)

While you're in that tools|Options|transition tab, you may want to turn off all
those "helpful" Lotus 123 settings--especially if you haven't use (or don't use)
123 anymore.