Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Is there any way to make Excel show a negative time span? If I subtract two time values (say, when subtracting the actual amount of time spent on something from the amount of time planned for it) and the result is negative, Excel just fills the result cell with hashes to notify me that the result cannot be displayed as a time value. Even OpenOffice.org Calc and Google Spreadsheets can display negative time values.

Is there a way to work around that issue by using conditional formatting? I really don't want to create some workaround by calculating the hours and minutes myself or anything like that.

share|improve this question

migrated from stackoverflow.com Nov 1 '10 at 12:39

This question came from our site for professional and enthusiast programmers.

add comment

4 Answers 4

By default, Excel uses a date system that begins with January 1, 1900. A negative time value generates a date-time combination that falls before this date, which is invalid.

The solution is to use the 1904 date system. Select Tools, Options. In the Options dialog box, click the Calculation tab and check the 1904 date system option to change the starting date to January 2, 1904. Your negative times will now be displayed correctly.

If you use the 1904 date system, be careful when linking to date cells in other workbooks. If the linked workbook uses the 1900 date system, the dates retrieved by the links will be incorrect.

share|improve this answer
    
Great! It works. Though it looks a bit hacky to me. But that's probably okay, I guess. :-) –  Pepor Sep 23 '08 at 8:00
add comment

Just check which is the larger time and subtract the smaller one from that:

If B1 and B2 are your planned and actual times, then the formula would be

=IF(B2 < B1,B1-B2,B2-B1)
share|improve this answer
add comment

Have you considered using conditional formatting to highlight negative time spans?

Im aware that this probably isn't a technically correct answer, but it will allow the user/reader to see at a glance whichspans are negative.

share|improve this answer
    
Yes, I already use conditional formatting to highlight negative time spans. The problem is that Excel just shows a lot of hashes istead of a number, which is not so great. –  Pepor Sep 23 '08 at 8:01
add comment

If the answers appear in a column of cells starting "A1" Hide that column from view. Then "insert" another column of cells beside the hidden column. Go to the cell at the top of the new column and type the formula =IF((A1<0,-A1,A1) This will always produce a readable answer. However it will not be obvious to tell if the correct answer was orriginally positive or negative. You need a mechanism to show this. An ideal method would be to include a further condtion in the formula changing the colour to red if A1 is less than zero

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.