Target times

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

I can't believe I can't do this - it must be so simple
I have a target time in Cell c40
I have elapsed times in cells b1-b31
by formula =sum(b1-$c$40) I get times that exceed the
target
but if the elapsed time is lower than the target I get
lots of hash (#) symbols.
I know there is a way of showing a "minus" time but I
just can't figure it out from the "help" and from
my "using Excel 97" book.
Thanks
 
Bill,

If the negative time means it goes over midnight. For instance, if 7:00 and
8:00 should return 23 hours, then use =MOD(B1-$C$40,1)

If you just want the absolute difference, then use =ABS(B1-$C$40)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Bill!

A different approach:

Return the absolute value of the calculation then
conditional format the cell to show a negative result. For
example:

A1 = target time = 7:10
A2 = actual time = 7:00

Formula in cell =ABS(A2-A1) returns 0:10:00

Conditional format to show negative times in bold red:
Formula is: =A2-A1<0

Biff
 
Thanks everyone - how do you get time???
Clearly not as easy as I thought.

I need to expand on the =abs by showing a minus figure in
the total if the result is better than target
ie target (c40) is 15:00 mins. Achieved (b1)is 12:00 mins
so result is -03:00 mins i.e. 3 minutes under target.
This will be graphed with target of 15 mins as the base
line "zero" with the y axis showing the minutes over
target above the base line and minutes uner target
(better than target) below the base line zero.
example
mins (- = better than target)
5
4
3
2 *
1 *
0------------------------------------- target
-1 *
-2
-3 *

day1 day2 day3 day4

Biff's example of conditional formatting gave result
True/False but if it gave the actual figure in
plus/minus format that would have been the business.
I tried all sort to modify to suit but ended up with no
sleep!!
Thanks so much
Bill

---Original Message-----
 
Is this any good?

=IF(F1>G1,"-","")&TEXT(ABS(G1-F1),"[hh]:mm")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top