Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    May 2003
    Location
    Germany
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Negative number of hours (Excel 2000)

    Does anyone know how to make Excel show a negative amount of hours when subtracting a larger number from a smaller? For example, 40:00

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

    Re: Negative number of hours (Excel 2000)

    The time formats in Excel do not work with negative numbers. You can use a formula to display negative times.

    Say that A1 contains the first time, and B1 the second time.
    C1 contains the formula =A1-B1. C1 should be formatted to General, or some other number format. This cell can be used for further calculations.
    In cell D1 (or any other cell), enter this formula:
    =IF(C1<0,"-","")&TEXT(ABS(C1),"[hh]:mm")
    This cell displays the negative time correctly. It is a text value, however, so you cannot use it directly in calculations.

  3. #3
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative number of hours (Excel 2000)

    A yucky workaround that gets half way there is to use a custom format

    [h]:mm:ss;-#,##0.00

    This displays positive hours properly, and negative ones as a fraction of days. At least it can be used for maths in things such as totalling time.

    An alternative is to keep the sign and number as separate cells

    =abs(A1-B1) in one
    and
    =A1<B1 in the other

    SUMPRODUCT or array formulae can then deal with the numbers for totals and the like.

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

    Re: Negative number of hours (Excel 2000)

    I'm not sure I like the custom format, but I use the method of keeping sign and absolute value in separate cells myself in some situations.

  5. #5
    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: Negative number of hours (Excel 2000)

    Tools - option - calculation (tab) - check "1904 date system" and you can work with negative time

    Steve

  6. #6
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative number of hours (Excel 2000)

    Steve, I thought I'd read somewhere that this was a bug and contrary to design?
    (But hey, if it works, it works.)

  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: Negative number of hours (Excel 2000)

    I am not sure if it is "bug", it smacks of more "design".

    I think it is allowed since excel works with dates from 1900 to 9999. If you pick the old 1904 date system, the first 4 years are negative (since day 1 is Jan 1, 1904), so it must be able to handle "negative date/time" with that system.

    I am not sure "old dates" get "converted" so be aware that some sheets might be 4 years off if you entered dates in the other system and then changed the option for the workbook.

    Steve

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

    Re: Negative number of hours (Excel 2000)

    Yes, existing dates are shifted by 4 years if you switch between the 1900 and 1904 date systems.

  9. #9
    New Lounger
    Join Date
    May 2003
    Location
    Germany
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative number of hours (Excel 2000)

    Thanks very much to everyone for all these responses. I'm trying out the option that Steve suggested first, simply because it looks the easiest. I was relieved to see that the option change only affects the current file. The fact that existing dates are increased by four years is not a problem - I have adjusted that in my test file. I am making a timesheet which is to be used by everyone in my company, and as some of them are very non-computer-literate I am trying to keep things as transparent as possible.

    I will try out the other methods as well, just in case the option change causes problems and I have to reverse it later on.

    Thanks again.

    June

Posting Permissions

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