Excel doesn't show negative time by default. But you can twist that with 3 methods. They all have their pros and cons.
- 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.
- 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.
- 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
- 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,"")
07/02/2025 @ 17:01
There's a better solution:
1. In a nearby cell, Cell B, calculate SIGN(my_formula), where my_formula is the thing that's giving you a negative time.
2. In the original cell, Cell A, multiply by Cell B.
3. Apply conditional formatting to Cell A. The condition is that Cell B is negative and the format is a custom number format of "[Red]-[hh]:mm" or "[Red]-[hh]:mm:ss" (without the quote marks).
4. If any cells are based on Cell A's value, change them so that they're based on (Cell A * Cell B).
The result is that Cell A contains a positive time, but displays it in red with a minus sign in front of it, and all downstream cells still work correctly. So long as you remember to use (Cell A * Cell B) in new cells based on Cell A, you're fine. Also, be careful with blind or colourblind colleagues who might not be able to easily tell that Cell A's text is red and that it's meant to represent a negative value.
Note that you don't need one Cell B per Cell A, but merely one Cell B per set of Cells A that necessarily all have the same sign (be careful!), though it's helpful if you can have one Cell B per Cell A and lay them out in the same shape, so that you only need one conditional formatting rule if you want to use a relative reference (for ease of copying - I use this trick for my timesheet spreadsheet, adding new rows each day), rather than needing to aim each Cell A at a joint Cell B.
29/11/2024 @ 07:51
my favourite solution is to display time as an integer when negative only. applying following custom format:
[h]:mm:ss;-#.##0,##;0;@
this allows to do all required calculations on timestamps without having to resort to cumbersome conversions, and works just as fine in pivot tables and others.
29/11/2024 @ 09:01
Clever 😃👍