Calculating time values
 

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?

Calculating time values
 

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.

--
Thanks,
Shane Devenshire


"Tom D" wrote:

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?

Calculating time values
 

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


"Tom D" wrote:

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?