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
- Go to File > Options
- Menu Advanced
- 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
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
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
Step 4: Sum of the 2 columns
- Let's do the SUM of each column
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,"")