Excel doesn't show negative time by default. But you can twist that with 3 methods. They all have their pros and cons.

1. Change the value of one Excel Option. By changing the first date of Excel, you solve the problem. But this option can impact all your dates in your workbook.
2. Change the Type of Data. If you apply the General format instead of the Time format, you can see the negative time, but the result is not understandable.
3. Create a Balanced Timesheet. As for a Bank statement, you can calculate the positive times in one column and the negative times in another column. And calculate the difference between both columns

How does Excel display negative hours?

When you subtract 2 durations, and the result should be negative, Excel returns #################

It's not a problem of columns being too narrow, like for a date. It's just by design, Excel doesn't show negative time.

Method 1: Change the Excel internal calendar

• In Excel, the very first day is 01/01/1900
• But, for compatibility reasons with Mac, another calendar exists. And this one starts the 01/01/1904.

To change the internal calendar

1. Go to File > Options
2. Menu Advanced
3. Check Use 1904 Date System

• Once you have checked this option, Excel shows negative hours
• But also, all the dates have been increased by 4 years 😱😱 (This is very dangerous for the accuracy of your document)

Method 2: Change the format number

If you update the format number to General, you will see a digital number.

The result is a decimal number because in Excel, a Time is between 0 and 1.

• Now the result is visible, and not the ##########.
• Proof that the problem comes from the Time Format that doesn't manage negative hours.
• But, clearly, no one is able to read such a result.

Method 3: Create a Balance sheet

The last method avoids all the previous cons but it's a little bit longer to create. Here we will create a document like a bank statement.

• One column for the exceeding time
• One column for the deficit time
• The total to present the result

Step 1: Create a column for the working time

• Let's start by subtracting the Ending time and the Starting time
• Also, let's remove one hour (1/24) for the lunchtime

=C5-B5-(1/24)

Step 2: Calculate Extra time ONLY

• Now, we will use the legal working time value
• We will compare the working time per day with the legal working time
• IF the result is positive, then we return the difference, otherwise, we return nothing

=IF($D5>$C$1,$D5-$C$1,"")

Step 3: Formula for working time under the legal time

• Same logic, but this time to return the value when the working time is under the legal working time

=IF($D5<$C$1,$C$1-$D5,"")

Step 4: Sum of the 2 columns

• Let's do the SUM of each column

=IF($D5<$C$1,$C$1-$D5,"")

Step 5: Compare the 2 Sum's result

• Create a test between the 2 sum's result
• According to the result, display the difference

Cell E14: =IF(E13<F13,F13-E13,"")

Cell F14: =IF(E13>F13,E13-F13,"")

Related Articles

• How to display hours over 24 in Excel?
• The TIME function in Excel
• Displaying Date and Time in the Same Cell
• Excel Split Date and Time