Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating Time with Date/Time checking in formula | Excel Worksheet Functions | |||
calculating timesheet, time-in/time-out = total hours & minutes, . | Excel Worksheet Functions | |||
Calculating time between values (tricky Problem) | Excel Discussion (Misc queries) | |||
Calculating time between values (tricky Problem) | Excel Worksheet Functions | |||
Calculating Time Values in Excel | Excel Worksheet Functions |