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 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.


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.

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

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.

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

Category: microsoft excel Time: 2008-08-22 Views: 1

Related post

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development


Front-end development


development tools

Open Platform

Javascript development

.NET development

cloud computing


Copyright (C), All Rights Reserved.

processed in 0.141 (s). 12 q(s)