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

=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,"")