Subtracting an earlier time from a later time , plus now()

[Steve Mc]

Can I get a time format to show a negative time, like -0:10

a1 = 4:20
a2 = 4:10
a1-a2 = :10
but a2-a1 = ##############
Changing the format to general will produce a negatine
number, but the time format just shows the ###'s.
Also, I assumed that now() would keep changing every
minute, however, whatever the time was when WS was opened
stays that time unless I close and re-open. Is there a
trick where the time now() cell changes as the actual time
changes while the worksheet is opened ?

Thanks,

Steve

 

[Jason Morin]

Although the cell displays #########, you can still use
this negative time value in calculations. To display a
negative time value, use this in another cell:

=TEXT(A1-A2,"-[mm]:ss")

HTH
Jason
Atlanta, GA

 

[Richard Stevens.]

To get the now() cell to change while the worksheet is
open you could press the F9 key to force a recalculation
of the spreadsheet.

Depending upon what you want to do with the result of the
a2-a1 formula:

you could use =ABS(a2-a1) and use conditional formatting
to display the time in red (or whatever format you choose)

and/or

use the above and then =IF(D2>D4,"earlier","later") in
the cell to the right of the result cell

For formulas dependent upon the result of a2-a1, you
could hide the result of a2-a1 in another cell and use
that (but not show the ########) or use IF functions (or
similar) to alter the dependent formulas.

I think I've explained it clearly! If not, email me if
you want to.

Cheers,
Richard