# displaying negative time

#### Met Fan

##### Board Regular
if I have expected hours of 45:00 and actual hours of 42:00 how can I display the result of their difference being 2:00 hours?

I can do a simple formula when the result is positive but not when it is negative.

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### Gerald Higgins

##### Well-known Member
This is going to be difficult, for two reasons.

First, the difference is not 2 hours, it's 3 hours

Second, Excel struggles with negative time.
If you just want to display a negative result, and are not going to use that result for any other calculation, you could do something like this
Code:
``=(A1-A2)*24``
and format the result as number, with 2 decimal places, to show -3.00.

BUT, if you start having fractions of hours, such as 3h 30m, this would show as -3.50, not -3.30.
There are ways round this, but it gets complicated.

Perhaps the best solution is to use something like
Code:
``=max(a1,a2)-min(a1,a2)``
You could then have another formula which displays a message if the max and min are not the normally expected way round.

Replies
3
Views
540
Replies
0
Views
342
Replies
2
Views
315
Replies
4
Views
228
Replies
4
Views
722

1,191,216
Messages
5,985,318
Members
439,956
Latest member
venky2002

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back