Show time as a negative figure

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
 
B

Bernard Liengme

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
 
H

Hans Terkelsen

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top