Negative time displaying as #####
 

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
--
Carla

Negative time displaying as #####
 

Try widening the column or reducing the font size

"Carla" wrote:

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
--
Carla

Negative time displaying as #####
 

If you want to display negative times, you need to use the 1904 date system.
--
David Biddulph

"Carla" wrote in message
...
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
--
Carla

Negative time displaying as #####
 

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
------
Convert your Excel spreadsheet into an online calculator.
http://www.spreadsheetconverter.com




--
Chris Bode

Negative time displaying as #####
 

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


"David Biddulph" wrote:

If you want to display negative times, you need to use the 1904 date system.
--
David Biddulph

"Carla" wrote in message
...
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
--
Carla

Negative time displaying as #####
 

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

--
Petr Bezucha


"Carla" wrote:

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


"David Biddulph" wrote:

If you want to display negative times, you need to use the 1904 date system.
--
David Biddulph

"Carla" wrote in message
...
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
--
Carla

Negative time displaying as #####
 

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

Carla wrote:

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

Negative time displaying as #####
 

Thanks Petr for your assistance, much appreciated. Such a simple answer!!
Regards
--
Carla


"PBezucha" wrote:

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

--
Petr Bezucha


"Carla" wrote:

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


"David Biddulph" wrote:

If you want to display negative times, you need to use the 1904 date system.
--
David Biddulph

"Carla" wrote in message
...
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
--
Carla

Negative time displaying as #####
 

Thanks Bob. This worked, but wasn't the result I was after. Appreciate your
help.
Regards
--
Carla


"Bob I" wrote:

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

Carla wrote:

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