If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Show time as a negative figure
Can someone tell me if it's possible to calculate time as a negative figure. I'm trying to do a calculation where if you work less than contracutual hours it shows up as -01:00 using the custom time format [h]mm:ss. To be clear, I have a figure of 0:00:00 (zero hours) and a figure of 1:00:00 (1 hour) but when I do the =sum subtraction formula it comes back with the result of #######. All I need it to show is 0 hours minus 1 hour = -1 hour. Any help much appreciated. JCR -- JCR1987 |
#2
|
|||
|
|||
Show time as a negative figure
Not is is not possible to work with negative time. Excel has no way of
handling this. The formula below will subtract B1 for A1 when A1 is the larger value. When that is not true, it subtracts the other way and displays the result as text with a leading minus sign. Note that you cannot do any math on the latter result - it just text. =IF(B1A1,"-"&TEXT(B1-A1,"hh:mm:ss"),A1-B1) By the way: you mention "=sum subtraction " If you meant something like =SUM(B1-A1), please be advised that SUM is totally unnecessary; one just need =B1-A1. The SUM function is used to find the sum of ranges as in =SUM(A1:F10) best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "JCR1987" wrote in message ... Can someone tell me if it's possible to calculate time as a negative figure. I'm trying to do a calculation where if you work less than contracutual hours it shows up as -01:00 using the custom time format [h]mm:ss. To be clear, I have a figure of 0:00:00 (zero hours) and a figure of 1:00:00 (1 hour) but when I do the =sum subtraction formula it comes back with the result of #######. All I need it to show is 0 hours minus 1 hour = -1 hour. Any help much appreciated. JCR -- JCR1987 |
#3
|
|||
|
|||
Show time as a negative figure
See if this doesn't help, but do use appropriate caution:
http://www.j-walk.com/ss/excel/usertips/tip051.htm "JCR1987" wrote: Can someone tell me if it's possible to calculate time as a negative figure. I'm trying to do a calculation where if you work less than contracutual hours it shows up as -01:00 using the custom time format [h]mm:ss. To be clear, I have a figure of 0:00:00 (zero hours) and a figure of 1:00:00 (1 hour) but when I do the =sum subtraction formula it comes back with the result of #######. All I need it to show is 0 hours minus 1 hour = -1 hour. Any help much appreciated. JCR -- JCR1987 . |
#4
|
|||
|
|||
Show time as a negative figure
"JCR1987" wrote in message ... Can someone tell me if it's possible to calculate time as a negative figure. I'm trying to do a calculation where if you work less than contracutual hours it shows up as -01:00 using the custom time format [h]mm:ss. To be clear, I have a figure of 0:00:00 (zero hours) and a figure of 1:00:00 (1 hour) but when I do the =sum subtraction formula it comes back with the result of #######. All I need it to show is 0 hours minus 1 hour = -1 hour. Any help much appreciated. JCR Hi JCR. Try this format, if you want to stay in the 1900 datesystem. [h]:mm;-[h]:"hrs" It is impossible to have negative minutes too, in the 1900 system, but if all you want is negative hours that format will work. Hans T. |
Thread Tools | |
Display Modes | |
|
|