PDA

View Full Version : Excel Negative Time Format



Emily
12-25-2008, 08:48 PM
I have one interesting question about Excel negative time format.

When I input these keys:

Spacebar
:
Spacebar 'Edit: No need
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.mrexcel.com/forum/showthread.php?p=1785914#post1785914

Aussiebear
12-26-2008, 03:34 AM
Interesting..... How did you come by this sequence?

Emily
12-26-2008, 05:18 AM
Read from China Excel forum

Aussiebear
12-26-2008, 01:25 PM
Good thing you edited the thread to show the cross posting.

Emily
12-27-2008, 07:27 PM
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

Cyberdude
12-29-2008, 03:47 PM
Hmmmm, I don't get anything but what I type, as expected.

Could it have something to do with the fact that Ctrl + : produces the date?? Or that Ctrl + Shift + : produces the time of day?? :dunno

Artik
12-31-2008, 07:49 AM
I talked about this problem on polish forum (http://www.excelforum.pl/viewtopic.php?t=10421).

See, what Jonh Walkenbach says about this (http://www.excelforum.pl/viewtopic.php?p=54342]this).

Is said in Poland: "There is as in czech movie - nobody nothing knows" :)

Artik

Emily
12-31-2008, 10:14 PM
thanks,
it's hard to understand the translation :banghead:

you mean this



I don't have an answer, but here's another weird one. Try appending am or pm
to those examples.

Enter " :0 am" (no quotes) and you get 2982616.16248843.

I googled for the integer portion of that number, and found this:

http://www.flickr.com/photos/erikeckel/2608146822/ (http://www.flickr.com/photos/erikeckel/2608146822/)

2,982,616 days is pretty close to 4,294,967,295 minutes -- which is
(2^32)-1.

Someone who understands how time values are stored internally could probably
explain this. Or maybe not.

-John

John article : http://office.microsoft.com/en-us/excel/HA102874951033.aspx

Still not understand Excel, anyway just for interest. Thanks

n8Mills
12-31-2008, 10:27 PM
Dare I ask... what's the point?!?!