How do i display a negative time as a res of distracting 2 times?

[Guest]

In a sheet about working time i want to extract two times.
1 is the normal working hours for the day
2 is the actual worked hours on a day
If 2 < 1, the result is negative - I worked not enough hours on that day.
The result is displayed as ######.

Question:
Is it posible to display negative hours c.q. times in a sheet?

 

[vezerid]

If you can settle for a text result,

=IF(A2-A1<0,"-","")&TEXT(ABS(A2-A1),"hh:mm")

HTH
Kostis Vezerides

 

[Bernie Deitrick]

Red,

Visit John Walkenbach's site:

http://www.j-walk.com/ss/excel/usertips/tip051.htm

***************************
Dealing with negative time values

Because Excel stores dates and times as numeric values, it's possible to add or subtract one from
the other.

However, if you have a workbook containing only times (no dates), you may have discovered that
subtracting one time from another doesn't always work. Negative time values appear as a series of
hash marks (########), even though you've assigned the [h]:mm format to the cells.

By default, Excel uses a date system that begins with January 1, 1900. A negative time value
generates a date/time combination that falls before this date, which is invalid.

The solution is to use the optional 1904 date system. Select Tools, Options, click the Calculation
tab, and check the 1904 date system box to change the starting date to January 2, 1904. Your
negative times will now be displayed correctly, as shown below.

Be careful if you workbook contains links to other files that don't use the 1904 date system. In
such a case, the mismatch of date systems could cause erroneous results.

***********************************

HTH,
Bernie
MS Excel MVP