Re: Negative time, using [h]:mm?

[<vegard bakke alliedpress co nz>]

Jody Goldberg wrote:
> On Thu, Jul 05, 2001, vegard bakke alliedpress co nz wrote:
>
> > I know Excel has the same problem, but it's fixable by changing your
> > type of calender.  How can you fix it in Gnumeric?
>
> hmm, this is tricky.  We would like to stay format compatible with
> MS Excel.  Can you expand on 'changing your calendar' ?

Okay I was wrong. You change the _date_format_, not calendar.  
(The only knowledge I had was from usenet, not my own experience. ;)


Try searching for:  '"[h]:mm" negative 1904'
on http://groups.google.com  


One reply gave this sollution:

http://groups.google.com/groups?q=%22%5Bh%5D:mm%22+negative+%26+1904&num=30&hl=en&rnum=29&ic=1&selm=VA.0000022d.10a5c802%40msn.com
--------------------------------------------------------------------------
From: Bill Manville (Bill-Manville msn com)
Subject: Re: negative time? 
Newsgroups: microsoft.public.excel.programming
Date: 2000/01/13 


In article <387DC372 3DAFFB77 alcatel de>, Dusan Malenka wrote:
> I need to make some operations with time, but when result is
> negative in cell value "########" appears . Is it  possible to set cell
> format
> to display negative time (for example:   "-10:25").
Excel does not normally recognise the concept of negative time.

If you set Tools / Options / Calculation / 1904 date format
you can get negative time.  However, as others have pointed out in 
another thread, there are problems if you also have dates in the
workbook 
that you reference from or copy to a workbook using the usual 1900 
format.

The alternative is to display the time as a text string (noting that you 
will be unable to do arithmetic on it).  If you current formula is
=A1-B1 change it to
=IF(A1>=B1),"-","")&TEXT(IF(A1>=B1,A1-B1,B1-A1),"[h]:mm")

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply to newsgroup
---------------------------------------------------------------------------

Cheers,

Vegard

-- 
Vegard Bakke       | My spelling is Wobbly. It's good spelling but it
54 Craigleith St   | wobbles, and the letters get in the wrong places.
Dunedin            |                                  Winnie the Pooh
New Zealand        |
The Land of Bæææ   |       http://www.bigfoot.com/~Vegard.Bakke/
Tel: (03) 473 1535 |  Vegard Bakke bigfoot com - Will always find me

The trouble with the global village are all the global village idiots. 
                                                         Paul Ginsparg
:wq