Forum Discussion

Lynn_vdb's avatar
Lynn_vdb
Copper Contributor
Sep 29, 2020

How to display negative amount of time

Hi everyone,

 

I’m making a worktime sheet for my husband.

I’m not having any trouble deducting his start and stop times, but I made a column for the amount of hours he’s supposed to work, and a column for the hours he effectively worked. And if the result of this deduction is a negative number (for instance if he was supposed to work 8h but only did 7,5) Excell won’t show me the negative number... which should be -30min.

 

is there a solution for this problem?

thanks!

excel
office 365

4 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Sep 29, 2020

    Lynn_vdb 

    Time is always time, we can't go to the past and shift time back. It's always positive. However, you may show time difference not in terms of time, but in terms of hours or days or minutes difference between two events. Taking into account that  1 day is equal to 1, thus 1 hour is equal to 1/24, you may deduct time of event happened at 07:30 from one happened at 08:00, multiply difference on 24 and the result will be -0.5 if you apply General format to it. That means -1/2 hours difference.

     

    Other words, =(B1-A1)*24 with applying General format to the result.

  • JMB17's avatar
    JMB17
    Bronze Contributor
    Sep 29, 2020
    Maybe, for the formula, you could use absolute value (ABS function), but then use conditional formatting to test if he worked less than what he was supposed to and apply a custom number format of

    -h:mm

    or something similar. So, the cell contents will still be positive, but the formatting will display it as negative? If you need totals, then you would have to use sumif functions to test if the actual time is <=> the expected time and net the results.

    Maybe someone else will have a better suggestion. I can't say I've ever tried to work with negative time values.
  • PReagan's avatar
    PReagan
    Bronze Contributor
    Sep 29, 2020

    Hello Lynn_vdb,

     

    Assuming the actual end time is in cell A1, the anticipated end time is in cell B1, and the difference is in cell C1, then that could be:

    For hours:

    (A1-B1)*24

     For minutes:

    (A1-B1)*1440

    Also, be sure that cell C1 is formatted as either General or Number.

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Sep 29, 2020

    Lynn_vdb 

     

    Change the way negative numbers are displayed

    Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel 2019 Excel 2016 Excel 2019 for Mac Excel 2013 Excel 2010 Excel 2007 Excel 2016 for Mac Excel for Mac 2011

    https://support.microsoft.com/en-gb/office/change-the-way-negative-numbers-are-displayed-104b47a6-8ae2-471f-8f73-c4b96e7bb146?ui=en-us&rs=en-gb&ad=gb

    Create a custom number format

    https://support.microsoft.com/en-us/office/create-a-custom-number-format-78f2a361-936b-4c03-8772-09fab54be7f4

     

    I would be happy to know if I could help.

     

    Nikolino

    I know I don't know anything (Socrates)

     

    * Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

     

Resources