+ Reply to Thread
Results 1 to 4 of 4

Negative Hours - Formula shows both 00:00:00 and -00:00:00

  1. #1
    Registered User
    Join Date
    08-13-2017
    Location
    Belfast, Ireland
    MS-Off Ver
    MS 2016
    Posts
    23

    Negative Hours - Formula shows both 00:00:00 and -00:00:00

    Hi

    I'm devising a time log for agency staff in my workplace, so that I can monitor their logged-on periods on our call-handling system to match up with claimed hours on their timesheets. The intention is to determine if they are working the full 7.5hrs (8hrs minus unpaid 30mins lunch) and making themselves available for 7.0hrs (taking into account their set tea breaks).

    I have checked and rechecked the formulae, and the cell format settings, and everything appears to be correct and consistent, with no errors or circular formulae, or anything else that might incorrectly result in a negative time. When all times are entered, everything seems to work fine, except in two columns with formulae - between both, there are six instances of 0hrs (formatted [hh]:mm:ss;@) with three of these showing as -00:00:00 and three as 00:00:00. They are all using the same formula pointing to the appropriate relative and absolute cell references (in the attachment, see cols K and M).

    Cells I2 to M22 are formula-based, with a 'ISBLANK' function built in to keep the table tidy. I would prefer to keep this in, so I have tried to nest another IF function in cols K and M to see if I could just make the 0hrs blank to remove the issue, but I can't get this to work.

    Is there something I am missing? The inputted times in D2 to H22 are all precise, there are no hidden digits etc that might cause a sneaky decimal somewhere to throw of the calculation, as it might do if they were ordinary numbers.

    Cells D2 to M22 are all custom-formatted with [hh]:mm:ss;@ so I can't think of what else is the cause; the negative issue was occurring when it was in the Time format of hh:mm:ss as well.

    At the end of the day I could live with the issue, however I'm at a loss to understand why it is happening even if it can't be fixed.

    Many thanks for your help in advance.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Negative Hours - Formula shows both 00:00:00 and -00:00:00

    Try using the round function

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Negative Hours - Formula shows both 00:00:00 and -00:00:00

    Quote Originally Posted by liamlarmour View Post
    When all times are entered, everything seems to work fine, except in two columns with formulae - between both, there are six instances of 0hrs (formatted [hh]:mm:ss;@) with three of these showing as -00:00:00
    This is a common anomaly of 64-bit binary floating-point arithmetic. Excel uses 64-bit binary floating-point to represent numeric values internally. Time is stored as a fraction of a day; for example, one hour is 1/24. And most decimal fractions cannot be represented exactly in binary floating-point. Consequently, binary arithmetic often results in infinitesimal differences with decimal arithmetic that we do manually.

    In general, whenever we expect a calculation to be accurate to some degree of precision, we should explicit round to that degree of precision, and not to an arbitrary degree of precision.

    For regular numbers, usually the degree of precision is a number of decimal places. For example, we might write ROUND(A1-B1,2) if we want the result to be accurate to 2 decimal places.

    For time calculations, usually the degree of precision is a number of minutes or, in your case, seconds. For example, in M2, write --TEXT(L2-$A$14,"[h]:m:s"). The double negate ("--") converts text to a numeric value. The "[h]" allows for results of 24 hours or more. Even if the latter is not likely in your case, it is a good habit to get into.

    It is good practice to round all time calculations. For example, in L2, write --TEXT(H2-D2-F2-(E2+G2+N2+O2),"[h]:m:s").

    Note that it is not a good idea to round time calculations to an arbitrary number of decimal places by using ROUND. To demonstrate, if we write ROUND(L4-$A$14,7) in M4, note that M4=TIME(0,5,0) returns FALSE(!), even we also write ROUND(H4-D4-F4-(E4+G4+N4+O4),7) in L4.
    Last edited by joeu2004; 09-02-2017 at 03:15 PM. Reason: minor

  4. #4
    Registered User
    Join Date
    08-13-2017
    Location
    Belfast, Ireland
    MS-Off Ver
    MS 2016
    Posts
    23

    Re: Negative Hours - Formula shows both 00:00:00 and -00:00:00

    Thanks mehmetcik and joeu2004 for your quick responses, I appreciate that very much. Apologies for not replying sooner, been on awkward shifts.

    I tried both solutions out and the ROUND([],7) solution worked perfectly on Cols K and M, the TEXT() solution didn't remove the '-00:00:00' issue.

    Many thanks though for taking the time to reply and offer both solutions, very much appreciated.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Histogram shows negative value where I dont want it
    By Annatw in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-08-2016, 03:36 AM
  2. If formula that shows hours within first instance of range
    By aftabn10 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-18-2015, 08:04 PM
  3. [SOLVED] TextBox - Display hours greater than 24 and negative hours too.
    By gnaske in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-21-2014, 03:01 PM
  4. [SOLVED] More grief over Hours v TIME- Negative hours
    By wyndland in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-25-2013, 11:49 AM
  5. cell shows 20. Formula shows 20. why not 540/27
    By William Horton in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 02:05 PM
  6. [SOLVED] Negative numbers shows up as )8.20( instead of (8.20)
    By JoyG in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-22-2005, 06:05 AM
  7. How can I show hours of more than 24 & negative hours?
    By Peterlg in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-18-2005, 01:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1