Show time as a negative figure

  • Thread starter Thread starter JCR1987
  • Start date Start date
J

JCR1987

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(B1>A1,"-"&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
 
JCR1987 said:
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
Click to expand...

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.
 
Back
Top