Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Displaying negative time | Excel Discussion (Misc queries) | |||
Displaying negative [h]:mm:ss | Excel Discussion (Misc queries) | |||
Displaying negative hh:mm:ss | Excel Discussion (Misc queries) | |||
Displaying negative time in excel | Excel Worksheet Functions | |||
More problems displaying a negative time | Excel Discussion (Misc queries) |