Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 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 Nov 1 '10 at 12:39

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

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
In Excel 2010 this setting is here: File -> Options -> Advanced -> When calculating this workbook – flash Jun 12 '15 at 7:43

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


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

share|improve this answer

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

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


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

share|improve this answer

You must log in to answer this question.