Displaying Negative Time

Carolina Chris

New Member
Joined
Dec 8, 2010
Messages
12
I have used a formula to subtract two dates and times. The fields are formatted in a Custom view (mm/dd/yyyy h:mm). When I subtract these two, I am trying to get the difference to display in terms of hours and minutes (hh:mm). The problem is when I have a negative difference. It displays pound signs (#) across the cell. I tried changing the formatting but none show the difference in terms of hours and minutes (it changes to decimals). I tried creating a field and using the absolute value of the times which does display the number but, since all of them are positive, it does not clearly show which ones actually had negative outcomes. As a last ditch effort, I tried going into Excel Options and changing the format to use the 1904 date system; the problem with this was that it changed all my dates in the sheet and not just the differences.

WHEW! Now to my question, is it even possible to display these times as negative without any pound signs (#) and not disturb the other information in the sheet?
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Did you try and take the negative of the two dates. i.e. 1/31/10 minus 1/29/10 equals -2 or ####.... If you change your formula to somthing like -(1/31/10-1/29/10) your result becomes +2 which then can be formated as hours
 
Upvote 0
I hadn't until now, but I'm getting the same problem. When the difference is positive, there is no problem formatting from there. I want it to actually show the number as negative when it is negative instead of the pound signs. Thanks.
 
Upvote 0
1904 date system is the only way you can get "real" negative time. You could calculate the hours in decimals by multiplying the difference by 24 and then you just get a number, e.g. 8.5 instead of 8:30 (and -8.5 will display fine)
 
Upvote 0
You can use a formula that would look correct but in fact return a text value, e.g.

=IF(A2>B2,"-","")&TEXT(ABS(B2-A2),"[h]:mm")
 
Upvote 0

Forum statistics

Threads
1,216,091
Messages
6,128,775
Members
449,468
Latest member
AGreen17

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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