Excel doesn't show negative hours by default. But we can twist that with 3 methods:

• Change one Excel Option
• Change the format number
• Create a Balance timesheet

How Excel displays negative hours?

When you subtract 2 durations and the result is 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,"")