subtract hours

[Steve@Blackhawk]

I would like to subtract hours, not time of day and have it end up in h:mm
format. currently I am subtracting a cell with hours in h:mm format from a
cell in number (0 decimal places) format with the following formula:

=((HOUR(E18)*60)+MINUTE(E18)-($I$8*60))/60

As an example the cell E18 is 30 and cell I8 is 6:45 and my answer is
-23.75. Can I get this cell to format to -23:45 ? I am calculating Comp time
on a timesheet, so the number can be positive or negative.

Thanks,
Steve

 

[Biff]

Hi!

Not sure what you're doing, but.........

=((HOUR(E18)*60)+MINUTE(E18)-($I$8*60))/60
As an example the cell E18 is 30 and cell I8 is 6:45 and my answer is
-23.75.

Using the above, I get a result of -0.28125.

The Hour and Minute functions need time serial numbers as arguments. That
is, a fractional number from 0 to 1. Since there is no Hour 30 or Minute 30
that portion of the formula evaluates to 0. So you're getting this:

0-($I$8*60)/60

The only way to calculate negative time and have it formatted as TIME is to
use the 1904 date system but this can lead to more problems than it's worth.
Any current dates in your file will be off by 4 years!

You could display negative times as a TEXT representation. That would mean
that calculations on those values will be *much* more complicated!

You could do something like this:

Assume your formula returns -23.75:

=IF(your_formula<0,"-"&TEXT(ABS(your_formula)/24,"[h]:mm"),your_formula)

Format the cell as Custom: [h]:mm

Biff