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

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

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
.

"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.