Excel Negative Time Format

[Emily]

I have one interesting question about Excel negative time format.

When I input these keys:

Spacebar
:
Enter

Excel cell displays ######### (-0.0416666666666667)

Anyone knows why these four keys caused such result?

Thanks in advance
Emily

ps -1/24 will display -0.0416666666666667

Cross post: http://www.vbaexpress.com/forum/showthread.php?t=24418

 

[mole999]

as a guess the : is a time separator so maybe it interpreted as 00:00

though testing on a blank workbook

: = :

-0.0416666666666667 = as time = 00/01/1900 01:00:00

once i have made a cell time the rest of the book then converts 1/24 into 01/01/2024

 

[mole999]

OK, what was the fourth key

i saw spacebar, colon, enter

as for the hashes its negative time

 

[Emily]

: <--- two keys Shift + :

 

[Jeffrey Green]

When I see that, I usually find that one of the "times" involved is not properly formatted.

 

[Emily]

as a guess the : is a time separator so maybe it interpreted as 00:00

though testing on a blank workbook

: = :

-0.0416666666666667 = as time = 00/01/1900 01:00:00

once i have made a cell time the rest of the book then converts 1/24 into 01/01/2024

Elaborate more:

when input
Spacebar + : + 1 shows 00:59
Spacebar + : + 2 shows 01:59
...
Spacebar + : + 24 shows 23:59
Spacebar + : + 25 shows 01/01/1900 12:59:00 AM
...
Spacebar + : + 9999 shows 19/02/1901 02:59:00 PM

9999 is the maximum value that can display time format, and also

Spacebar + : + 0 shows -0.000694444444444444