Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    New Lounger
    Join Date
    Apr 2001
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Getting a negative '00:00' (2002)

    Hi

    I suspect this is going to be very obvious to some of you, but how on earth does Excel decide that -00:13 added to 00:13 makes -00:00. I have a workbook set up with twelve worksheets each holding a timesheet for one month. They were all set up at the same time with the same formulas and Jan-Oct have worked just fine. I can solve the practical problem by pasting the values to a new sheet using the earlier sheets as templates, but I am curious as to how this kind of thing happens.

    Thanks in advance.

    Lucy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Getting a negative '00:00' (2002)

    How did you get negative time values? As far as I know, Excel can only handle non-negative times.

  3. #3
    New Lounger
    Join Date
    Apr 2001
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting a negative '00:00' (2002)

    The cell is formatted as hh:mm under the Custom option. This allows us to calculate flexitime credited and debited. (See values below)

    I can try sending you a sample, if that would help.

    DATE IN OUT IN OUT HOURS LEAVE DAILY +/- HOURS +VE -VE NOTES

    1st 08:30 12:51 13:37 17:00 07:44 0:32 03:33
    2nd 10:00 12:40 02:40 -4:32 -00:59 Flexi pm

  4. #4
    New Lounger
    Join Date
    Apr 2001
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting a negative '00:00' (2002)

    Oops, that's why I should have used preview, isn't it? Hope the lack of tabulation doesn't make the info unintelligible.

    Sorry, Lucy

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Getting a negative '00:00' (2002)

    I still don't understand how you get -4:32 and -00:59. If I enter something like that in Excel, I either get an error message or it is interpreted as text. And what do they mean?

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Getting a negative '00:00' (2002)

    If you use the 1904 time system (tools -options - calculation), excel will accept negative times and dates.

    Steve

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Getting a negative '00:00' (2002)

    I don't understand the question. Excel can subtract 2 seemingly identical numbers and get "-0" it depends on the rounding and the rounding errors.

    If you change to a different format display you will see that you have a tiny negative number so you have a negative difference, (that is the undisplayed figures of "-00:13" are larger than the undisplayed figures of "00:13"

    Steve

  8. #8
    New Lounger
    Join Date
    Apr 2001
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting a negative '00:00' (2002)

    And I thought this was going to be straightforward <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>
    The problem is this:

    Until recently 'adding' the contents of two cells, eg -00:13 + 00:13 would have produced 00:00; now it produces -00:00.
    The reason that this is a problem is because adding -00:00 to the contents of the next cell down the worksheet produces the 'wrong' answer, whereas adding 00:00 to the contents of the next cell down produces the 'right' answer.
    In practice the errant minus sign means that someone who is in debit by 13 minutes on Day 1 of the month, works an extra 13 minutes on Day 2, and an extra 33 minutes on Day 3 will show a debit balance of 23 hours and 27 minutes (-23:27), when they should show a credit balance of thirty three minutes (00:33).

    This seems incredibly long-winded, I'm sorry I don't have the techy language or understanding required to explain it more clearly.

    Lucy

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Getting a negative '00:00' (2002)

    The only way that adding -0:00 to 0:33 will give -23:27 is if the -0:13 has an extra DAY onto it than the 0:13.
    In that case the -0:00 is actually the number -1 (= -24 hrs) whihc is why the "debit" is so large.

    Format the cells to general. I would wage that your "-0:00" is -1. Which means that your "-0:13" is actually "-1.009027" where your "0:13" is only "0.009027"

    Steve

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Getting a negative '00:00' (2002)

    Thanks, I didn't know that. I never use the 1904 system.

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Getting a negative '00:00' (2002)

    Neither do I. It is just one of those "tricks/techniques" I heard about at some time.

    Steve

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting a negative '00:00' (2002)

    Instead of formatting these cells as hh:mm, try using [hh]:mm (with the brackets around the hh). This will display elapsed time, which should show you when one of the values is greater than 24 hours.
    Legare Coleman

  13. #13
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting a negative '00:00' (2002)

    The 1900 date system is due to Lotus 1-2-3, which MS took over for compatibility. The 1904 date system is in fact the correct system (which is used on the Mac).
    Microsoft MVP - Excel

  14. #14
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting a negative '00:00' (2002)

    I'm curious, how can the 1904 start date be "the correct system" and the 1900 one be wrong? Are they not just different but arbitrary starting points?

    stuck

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Getting a negative '00:00' (2002)

    The 1900 is wrong since there is a Feb 29, 1900. 1900 is not a leap year since it is a century and not divisible by 400.

    All the "serialnumbers" before Mar 1, 1900 are "incorrect"

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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