How to sum negative times

[Nitroman3000]

Hi all,

I have the following formula for my worktime sheet:

=IF($H$2<=F3;(F3-$H$2);"-"&TEXT($H$2-F3;"[HH]:mm"))

Given that I have to work 8:24 hours a day (8 hours and 24 minutes)
with that formula I can check if I worked more or less 8:24.

Now I have to sum all days in a month: there are positive as well as
negative times. The problem is that only positive cells are summed -
the negative cells are not considered.

How can I solve this problem? E.g. (00:35) + (-00:24) = (00:09) (== 9
minutes)

 

[Gord Dibben]

http://spreadsheetpage.com/index.php/tip/dealing_with_negative_time_values/


Gord Dibben MS Excel MVP

 

[joeu2004]

I have the following formula for my worktime sheet:
=IF($H$2<=F3;(F3-$H$2);"-"&TEXT($H$2-F3;"[HH]:mm")) [....]
The problem is that only positive cells are summed -
the negative cells are not considered.

Note that "negative cells" are excluded when using SUM only because
they are text instead of numbers. SUM __will__ include negative time,
even if it is displayed as "####".

One solution: simply compute =F3-$H$2 in a parallel column, which you
can hide, and use __those__ values for your sum.

For example, suppose your work times are in F1:F5. Then in G1, put
the formula =IF($H$2<=F1;F1-$H$2;"-"&TEXT($H$2-F1;"[HH]:mm")) and copy
down through G5. And in H1, put the formula =F1-$H$2 and copy down
through H5.

Then compute the true sum in H6 as =SUM(H1:H5), and display the sum in
G6 using =IF(H6>=0;H6;"-"&TEXT(-H6;"[HH]:mm")).

Another solution: someone has pointed out that the Custom format [m]
will display negative time. But then all time is displayed in
minutes, not hh:mm. Probably not an acceptable solution.

PS: Do yourself a favor and resist any suggestion to set the
calculation option to "1904 date system". It will screw up any dates
you already have in the workbook. It will probably screw things up
any time you copy-and-paste between workbooks, unless you consistently
use the "1904 date system" option. Likewise, it will probably screw
things up if you link to date cells in other workbooks. Eventually,
you will forget that you set this unusual option.