# Display negative time in Excel

Last Updated on 02/12/2023

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 checkbox in the Excel Option

By changing the first date of Excel, you solve the problem.
But this option can have an impact on all your dates in your workbook

2. Change the Type of Data

If you apply the General format instead of 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 a 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
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

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

# Display negative time in Excel

Last Updated on 02/12/2023

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 checkbox in the Excel Option

By changing the first date of Excel, you solve the problem.
But this option can have an impact on all your dates in your workbook

2. Change the Type of Data

If you apply the General format instead of 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 a 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
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

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