Display negative time in Excel

Display negative time in Excel
Last Updated on 02/12/2023
Reading time: 3 minutes

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

    Excel does not show negative hours

    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
    Menu calendar 1904
    • 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)
    Negative time is visible but dates have been modified

    Method 2: Change the format number

    If you update the format number to General, you will see a digital number.

    Exccel negative hours visible in General number format

    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
    TimeSheet Statement with negative hours

    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)

    Calculation of the working time

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

    Formula to calculate extra time

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

    Formula to calculate the time under the legal time

    Step 4: Sum of the 2 columns

    =IF($D5<$C$1,$C$1-$D5,"")

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

    Formula to return the balance between the 2 time columns

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    Display negative time in Excel

    Reading time: 3 minutes
    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 #################

      Excel does not show negative hours

      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
      Menu calendar 1904
      • 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)
      Negative time is visible but dates have been modified

      Method 2: Change the format number

      If you update the format number to General, you will see a digital number.

      Exccel negative hours visible in General number format

      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
      TimeSheet Statement with negative hours

      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)

      Calculation of the working time

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

      Formula to calculate extra time

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

      Formula to calculate the time under the legal time

      Step 4: Sum of the 2 columns

      =IF($D5<$C$1,$C$1-$D5,"")

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

      Formula to return the balance between the 2 time columns

      Leave a Reply

      Your email address will not be published. Required fields are marked *