26

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.

fixer1234
  • 28,064

7 Answers7

13

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.

fixer1234
  • 28,064
Espo
  • 324
4

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

Sathyajith Bhat
  • 62,374
frogspawn321
  • 41
1

I ran into this same issue myself recently, and while Google search got me to this question, I didn't feel that any of the existing answers really address it properly. But taking existing answers as a hint, I was able to do this in the following way:

Assume cell A1 contains the result of a calculation that gives a "negative time value". You can display a "sensible" time value (no ####) by entering the following in cell B1:

=IF(A1<0, TEXT(-A1, "-[H]:MM"), TEXT(A1, "+[H]:MM")

This turns the value into a string which you can format however you need - I chose to format with [H]:MM but you can use any valid Excel format. You do have to add the sign "manually".

Here is a screenshot of an application of this - where there is a cumulative sum of hours with occasional "payments" that can sometimes reduce the sum to a negative number. I expect that's quite a common thing, given some of the questions I have read on this topic.

enter image description here

The only drawback of this method is that the time being displayed is in fact "text". If you need to manipulate the actual time result (including negative values) you should use the value, not the formatted version. You can do this by having a hidden column (if you want to keep your sheet "pretty") - this column can then be used for additional calculations, graphs etc. In the image below, column F has the "real" value while column G contains the "formatted" value.

You can even use conditional formatting with a formula rule to color these text fields "as though they were numbers"... but that was not the question.

Floris
  • 1,189
1

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

suspectus
  • 5,008
Amer Jaradat
  • 11
0

=IF(B4-A4<0,(TEXT((24-B4)+(A4),"-H:MM:SS")),B4-A4)

This is the formula I used to solve negative time.

Aaron
  • 11
0

For completeness' sake, the cell format for something like 3:30:00 (3 hours and 30 minutes) would be h:mm:ss. For each individual component such as h, mm and ss, it is possible to display the negative value by surrounding them with square brackets.

Suppose we calculate 3:30:00 - 3:35:00, giving us negative 5 minutes. Excel then displays the following values depending on the cell formatting:

  • h:mm:ss: error
  • h: still error
  • [h]: -0
  • [m]: -5
  • [mm]: -05
  • [ss]: -300
  • [h]:mm:ss: error
  • [h]:[mm]:[ss]: error

Screenshot of the cell format dialog in Excel. Within the Number tab we set a custom format for the cell to [mm] which displays the negative time difference in minutes.

Unfortunately, combining these formats (as demonstrated in the last two examples) does not seem to work. The best bet would be to use a formula like =TEXT(A1,"[h]")&":"&TEXT(ABS(A1),"mm:ss"). Through this process, however, the numeric value would be turned into a text value again.

Felix Jassler
  • 148
0

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.

TK.
  • 111