Date Format Challenge

[Michael]

Hi Folks - I'm using Excel 2002. Try this challenge in a new blank
worksheet:

Enter 10:15 in A1
Enter 10:30 in A2
Enter 10:45 in A3

Enter 1 in A4

Excel will automatically treat the 1 as a date. Why? I did not format the
column as a date. Or, is Excel looking at the previous entries and assumes I
need a date.

Thansk.

Mike

 

[Peo Sjoblom]

Not for me, if I do that it returns 00:00 in A4 as expected since it thinks
I enter times and 1 is 24/24

 

[Bill Sharpe]

Excel 2002 appears to be a bit quirky here.
If I enter 10:15, 10:30, and 10:45 in cells A1, A2, and A3 and 1 in A4, I
get 0:00 in A4 -- time format.
If I enter 10:15 in B1, 10:30 in B2, and 1 in B3, I get 1 in B3 -- general
format.
If I enter 10:15 in C1 and 1 in C2, I get 0:00 in C2 -- time format again.

Hardly seems consistent to me... Perhaps Microsoft is following Emerson's
philosophy -- "A foolish consistency is the hobgoblin of little minds."

Quattro Pro, on the other hand, will put the expected 1 in A4, B3, and C2.

Bill

 

[Michael]

I should have been more specific .... When I entered 1 in A4, it formatted
it as date/time format, 1/1/1900 12:00:00 AM. I understand why A4 would
display that formatting IF I HAD FORMATTED THAT CELL FOR DATE/TIME (not
yelling here, just emphasing, <grin>). What I don't understand is why Excel
formatted A4 as date/time. Any ideas?

Mike

 

[jmurck]

In Excel 2000 this is set in the 'Tools', 'Options', 'Edit' tab unde
option 'Extend list formats and formulas. If unchecked it enters dat
as entered instead of formatted

 

[Michael]

That's the ticket ...... Thanks.