date input

[Paul M.]

On one spreadsheet that was converted from Lotus to Excel 2003, when I input
a date (example - 02/15/2006) into a cell, Excel actually calculates it as a
formula by dividing the numbers. And since the cell is formatted to a date
format, I get 1/0/1900 showing in the cell with the =2/15/2006 showing in the
formula bar. Any ideas to get Excel to recognize the date as I input?

Thanks.

[Bryan Hessey]

I presumethat you converted this via a 123 format? - in which case the dates should have been input correctly. If so you need to check the output phase to see what, if any, (hidden) characters appear before the date.

If you are using an early version of Excel you might need to be very specific in the import. If you are using a .csv format then, instead of letting Excel recognise the file, open it via Data, Import External Data, and set the columns and date formats as required.

The zero day of January 1900 is a date = zero.

Any further clues?

--

On one spreadsheet that was converted from Lotus to Excel 2003, when I input
a date (example - 02/15/2006) into a cell, Excel actually calculates it as a
formula by dividing the numbers. And since the cell is formatted to a date
format, I get 1/0/1900 showing in the cell with the =2/15/2006 showing in the
formula bar. Any ideas to get Excel to recognize the date as I input?

Thanks.

[Gord Dibben]

Tools>Options>Transition.

Uncheck "transition formula evaluation" and other checkboxes while there.


Gord Dibben MS Excel MVP

On Fri, 21 Apr 2006 06:00:01 -0700, Paul M. <Paul [email protected]>
wrote:

>On one spreadsheet that was converted from Lotus to Excel 2003, when I input
>a date (example - 02/15/2006) into a cell, Excel actually calculates it as a
>formula by dividing the numbers. And since the cell is formatted to a date
>format, I get 1/0/1900 showing in the cell with the =2/15/2006 showing in the
>formula bar. Any ideas to get Excel to recognize the date as I input?
>
>Thanks.