Negative time displaying as #####

  • Thread starter Thread starter Carla
  • Start date Start date
C

Carla

Hi,

I currently have two times (formatted as general). Eg. 0525 (cell A2) and
0105 (cell B2). I have calculated the difference between the two times using
the following formula:
=TIME(LEFT(B2,2),RIGHT(B2,2),0) -TIME(LEFT(A2,2),RIGHT(A2,2),0) + (B2<A2)

The problem comes when I try to override either cell B2 or cell A2. The
output cell then gives me #### when the number is negative, or the wrong
result when it is positive. Even if I change the formatting of A2 and B2 to
custom (h:mm AM/PM), I get the same result (####) though I have also changed
the formula to a simple subtraction of B2-A2.

Does anyone have any suggestions - do I change the time format used, or the
subtraction formula?

Thanks in advance
 
Set the format of the cell showing the subtraction result as
..suppose the result is in C1..select c1 and enter formula
=TEXT(A1,"mm:ss")....

hope it helps

Chris
 
Hi David,
I have played around with the 1904 date system, the cell formatting used,
and the subtraction formula, but am not having much luck. Can you please
assist further if I provide the document I am working on?
Thanks
 
Carla,
You must not obtain a negative result at any price: time formatting of such
a value is strictly forbidden and ends with #####. Therefore there is a
bracketed tail in your correct formula, which adds a unit (True=1) in case
the subtrahend is greater. Your failure has been in wrong formatting of cells
A2 and B2: it must be TEXT, not GENERAL. You must see really: 0535, not
number: 535, otherwise your text based formula fails. With number (or
General, implicit as number) formatted cells, the formula would have to
acquire another shape.
Regards
 
Open a new sheet with 1904 "Unselected".

Plug 1:00 in A1
4:00 in B1
=A1-B1 into C1

Observe ##### is displayed

Now set 1904 in options

C1 should now show -3:00
 
Back
Top