Excel Negative Time Format

[WC Law]

When I input these keys:

Spacebar
:
Enter

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

-1/24 also displays -0.0416666666666667

Anyone knows why these four keys caused such result?




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

 

[Ron Rosenfeld]

When I input these keys:

Spacebar
:
Enter

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

-1/24 also displays -0.0416666666666667

Anyone knows why these four keys caused such result?




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

Your description takes some doing to understand.

If I enter the keys as you describe:

<spacebar>
+
:
+
1

The cell displays: + : + 1


So I suspect that is not what you are entering.

In any event, the following should enable you to understand what is going on:

"How Excel Handles Dates and Times"
http://office.microsoft.com/en-us/excel/HA102874951033.aspx

An excellent article from John Walkenbach.





--ron

 

[NickViv]

What problem is caused by this ?

rgds

Nick

 

[Shane Devenshire]

Hi,

I can not duplicate the first problem typing Spacebar : Enter simply enters
a colon in the cell with a space in front of it?

Cheers,
Shane Devenshire

 

[WC Law]

Hi,

I can not duplicate the first problem  typing Spacebar : Enter simply enters
a colon in the cell with a space in front of it?

Cheers,
Shane Devenshire












- Show quoted text -

Sorry misleading you

1. Spacebar
2. :
3. Enter


Spacebar + : + 9999

1. Spacebar
2. :
3. 9999
4. Enter

 

[WC Law]

Sorry misleading you

1. Spacebar
2. :
3. Enter

Spacebar + : + 9999

1. Spacebar
2. :
3. 9999
4. Enter- Hide quoted text -

- Show quoted text -

The article "How Excel Handles Dates and Times" does not have the
relation between "Spacebar" and "Colon"

 

[Dave Peterson]

If excel thinks you're entering a number (or a time), it'll remove those
leading/trailing space characters.

If you want to see that:
_:9999
(where _ represents a space character)

Then either preformat your cell as Text, then do the data entry.
or
Start your entry with an apostrophe:
'_:9999

But I'm not sure how you're getting a negative time.

Maybe you have an entry in the tools|autocorrect options|autocorrect tab list.

 

[WC Law]

If excel thinks you're entering a number (or a time), it'll remove those
leading/trailing space characters.

If you want to see that:
_:9999
(where _ represents a space character)

Then either preformat your cell as Text, then do the data entry.
or
Start your entry with an apostrophe:
'_:9999

But I'm not sure how you're getting a negative time.  

Maybe you have an entry in the tools|autocorrect options|autocorrect tab list.











--

Dave Peterson- Hide quoted text -

- Show quoted text -

I tried several PC, just enter _: and enter(_is space)
the result is same as you enter -1/24

 

[Dave Peterson]

No 9999's this time?

Do you have any "helpful" macros running?

If you open excel in safe mode, does it happen?

Close excel
windows start button|run
excel /safe

And test it to see what happens.

Starting in safe mode disables lots of things -- including macros.

WC Law wrote: