Date format in imported lotus 123 file

[Guest]

I imported a lotus 123 file for tracking investments. When I try to enter a
date Excel gives me back 1/0/00, which is the beginning of the 20th century.
I've tried several date formats to no avail. I also get a flag in the cell
saying the date string only has 2 digits for the year. The other data in the
sheet appears OK. The date format works OK on a clean sheet. I am using
Excel from Office 2003 Basic.

Help

 

[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.