Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    negative time (XL2000 sp2)

    Attached is a workbook with the 1904 date system enabled so that it can handle negative times. In it, cells A1:B2 are all are formatted to [h]:mm and cell A1 holds the value -29mins.

    You can autofill cell A1 down or across and the negative time will propagate. If you copy cell A1 and paste special the value into cells A2 or B1:B2 it will be OK but but if you try to edit cell A1, say make it -35mins, or enter a negative time value manually into A1:B2 you get an error message.

    Is there a trick out there to get Excel to accept a manually entered negative time?

    stuck

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

    Re: negative time (XL2000 sp2)

    Since Excel does not support negative time values, it does not recognize any negative time strings. The only way I can fine to enter a negative time value would be to convert the time into an Excel time value and enter that. For example, -12:29:00 AM would be entered as -0.0201388888888889.

    A second alternative would be to use a formula:

    <pre>=0-TIMEVALUE("12:29:00 AM")
    </pre>

    Legare Coleman

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

    Re: negative time (XL2000 sp2)

    But it does handle negative times, just not very well it seems! If you use the 1904 date system Excel will calculate a negative difference between two times and happily display the result but you can't enter a -negative time value manually into a cell.

    stuck

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

    Re: negative time (XL2000 sp2)

    Excel does not SUPPORT negative time value (an undefined concept). The fact that a quirk the 1904 date system allows negative time values could more correctly be described as a bug in the code.
    Legare Coleman

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

    Re: negative time (XL2000 sp2)

    OK, so writing the software for my time machine is going to take a bit longer than I thought.

    stuck

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

    Re: negative time (XL2000 sp2)

    Or some other programing language. <img src=/S/bash.gif border=0 alt=bash width=35 height=39> Never use force, get a bigger hammer. <img src=/S/yep.gif border=0 alt=yep width=15 height=15>
    Legare Coleman

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

    Re: negative time (XL2000 sp2)

    Thanks for the tip, I've got a bigger hammer now.

    My a work around involvies formatting the cell designed to accept -ve times as text then in another cell checking to see if it starts with a '-' and if so using your 0-timevalue suggestion. and passing that into the rest of the sheet.

    (un)stuck

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

    Re: negative time (XL2000 sp2)

    I'm pleased to report that this quirk in the 1094 data system that allows negative time values still works in XL 2007.

    Ken

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

    Re: negative time (XL2000 sp2)

    We're stuck <img src=/S/pun.gif border=0 alt=pun width=22 height=18> with that quirk for eternity, I guess.

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

    Re: negative time (XL2000 sp2)

    <img src=/S/yep.gif border=0 alt=yep width=15 height=15> I hope so - well at least until I retire, which is a fair few years away yet. I built a fairly involved workbook to handle my flexi-time and negative time is crucial to its working because the flex rules at my place of work allow me to go into deficit (by a modest number of hours) without penalty.

    Ken

Posting Permissions

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