Calculating time values

[Tom D]

I have a field that's calculated by taking a number of hours and multiplying
them by a percentage to get the correct number for that column. = $D$65*B60
is the formula in the field. the $D$65 is the total of the hours (in this
case, 24:00). The B60 is the percentage of hours for this field, which is
30%. This gives me 7:12 minutes for as a target for this field(Goal hours)

Above this field is a different time field that has accumulated hours
(Actual hours). what am looking to get is the Actual hours - the Goal
Hours, to reflect delta usage. Right now, the Actual hours is 3:32 The
Goal hours of 7:12, subtracted from the Actual hours should give me a
remaining hours total, in the negative. If the Actual hours exceed the Goal,
then it should show as a Positve.

How to I get this to work?

 

[ShaneDevenshire]

Hi,

If both times are actually in time units you can subtract. But if time
values are negative Excel wil not be happy. One way around this is just a
display issue,
use a formula like this:

=IF(E1<E2,"-"&TEXT(ABS(E1-E2),"h:mm:ss"),E1-E2)

Alternately you might work with the times as fractional parts of day.

 

[OssieMac]

Basically you can't have negative time. You can trick the system a little bit
by formatting the resulting cell as a number and it will display a negative
number.
Example
3:32 - 7:12 returns -0.152777777777778

You can then use the resulting number in conjunction with a time so long as
you don't get a negative result and try to display it as a negative time.
Example
7:12 - 0.152777777777778 returns 3:32


Regards,

OssieMac