Is there any way to properly display negative time spans in Excel?

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.

Solution 1:

From http://spreadsheetpage.com/index.php/tip/C17/P10/ by John Walkenbach:

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.

Solution 2:

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 originally positive or negative. You need a mechanism to show this. An ideal method would be to include a further condition in the formula changing the colour to red if A1 is less than zero

Solution 3:

I solved the problem as following:

assume that

E2 is starting time and F2 is finishing time ( format these cells in TIME formula , it doesn't matter 24 hr or Am/Pm )

then the formula will be

=TEXT((24-E2)+(F2),"H::MM")

format of the cell should be ( NUMBER)

this formula will help you if the starting time will be at evening in D day and finishing time in morning in next day. Good luck