Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jul 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Negative Time (All)

    When I try to subtract 2:30 from 4:15, I sould get -1:45.
    However, Excel returns "######".

    Is there a way I can get negative time values to work?

    Rlay

  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 (All)

    First, since 4:15-2:30 is 1:45, not -1:45, I will assume that what you meant is 2:15-4:30.

    Excel does not recognize negative times. So, you can not accomplish what you want using Excel time values. Since negative times are also not physically possible, can you explain what you are trying to do so we can see if there is some other way to accomplish it?
    Legare Coleman

  3. #3
    New Lounger
    Join Date
    Jul 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative Time (All)

    Yes, that is what I was meant to say.

    Essentially what I need it for is time sheets.

    Take for example an average working day is 8 hours.
    If the employee works 9 and a half hours, it will return (positive) 1 and a half hours.
    Alternatively if the employee only works for 7 hours, it SHOULD return a negative 1 hour.
    Overall, in the above example there is a total of (negative) half an hour owing.

    Is there any way around the above situation?

    Rlay

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative Time (All)

    My office uses a wierd timesheet too.

    Hva a look at the attached as it deals with negative time using a separate field that indicates +/-.

    I have another version of a timesheet that uses text only, and converts the text to/from time at calculation time. If you want it, I will try to find it too...
    Attached Files Attached Files

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative Time (All)

    You can have Excel calculate with negative times, but you have to change the date system for that: Tools, Options, Calculation, 1904 date system.

    This is a workbook-specific setting.

    ***WARNING***

    When you change this setting, all dates in your workbook will change (the underlying data -the DateSerial number- remains the same, but the displayed date and all calculations based on that date will change). To get the original dates back, subtract the number 1462 from them (or add if you're moving back of course).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative Time (All)

    Work-around w/o 1904 time system:

    Calculate/display the time deltas and/or running total as decimal number by multiplying the result (or the summands) by 24 _and_ formatting the cell as number (time is stored as a fraction of a day).

    Should you compute the wages you will need this value anyway. Case your users have difficulties with decimal hours i.e. 1.33 h ~ 1:20, convert the (key) numbers to an appropriate text string. Assuming your number in C9:

    =IF(C9<0,"- ","+ ") &TEXT(ABS(C9/24),"[h]:mm")

    If you need more information, just ask.

Posting Permissions

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