Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jan 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Entereing a negative time value (Excel 2000/XP)

    Hi All,

    I have a timesheet spreadsheet which is used by staff to calculate total hours worked per week.
    My problem is, I need to enter a negative time value for staff who are carrying over hours from the previous week so it is duducted from the current week to calculate total hours that need to be worked.
    When I enter -3:25:00 in a cell I get a excel warning message informing me that the formula I have entered contains an error.

    Sorry for the long winded post, I bet there is a very simple answer to this! (I hope)

    Thanks

    Naz

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

    Re: Entereing a negative time value (Excel 2000/XP)

    The way that Excel stores Date/Time values, it is impossible to store a negative time. Would it be possible to store the carry over in a cell as a positive value and then subtract that cell in the formula?
    Legare Coleman

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

    Re: Entereing a negative time value (Excel 2000/XP)

    Excel can't handle negative times, unless you set it to a 1904-based date system (Tools/Options..., Calculation tab), but that might have consequences you don't want. To display negative time differences, the standard trick is to calculate the absoulute value and a flag that indicates whether it is positive or negative. See for instance the timesheet attached to <post#=191457>post 191457</post#>.

    To enter negative time values, you could use two cells: one to enter the absolute time value, the other to enter a positive/negative flag (you could use a check box linked to a cell for this). You'd have to use both the time value and the flag in further calculations.

Posting Permissions

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