Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2007

    Unanswered: Calculating time differances


    Hoping this is a very simple question.

    I need to calculate the differance between a start time and a finish time, which I have done as

    =(C2-B2) for example C2 is finish time and B2 is start time

    But then I need to calculate how much this is above or below 5:00 hours.

    I've done this as:

    =(D2-I1) for example D2 is answer to above and I1 is 5:00

    This works for positive answers but negative time come up as ########

    How can I work out what time differences are above and below 5 hours?

    I'm doing this as a clocking in method for hours worked above or below 5 hours? (In a 24 hour clock format)

    Hope this all makes sence and someone can help


  2. #2
    Join Date
    Jan 2007
    Provided Answers: 10
    Data types involved please?

    If dates, then lookup DateAdd() and/or DateDiff()
    If integers, check out Abs()
    Home | Blog

  3. #3
    Join Date
    Oct 2003
    DateAdd and DateDiff are VBA functions, not native to Excel. So you would need the code installed and then reference the functions that way.

    See DateDiff VBA
    old, slow, and confused
    but at least I'm inconsistent!

    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  4. #4
    Join Date
    Dec 2007
    OK, i'll have a look into those function. Though i've never used VB in Excel before. I was kind of hoping i wouldnt need to get involved with VB, but if its the only way to make Excel have negative times i'll have to. Thanks, i'll let you know how i get on.

  5. #5
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 2
    You don't need to use VBA. Excel displays "######" when a cell contains a numeric value with more digits than can be fitted into the current width. All you need to do is resize the column to see the actual answer. (My mum used to impress her boss with this all the time!)

    With regard to the answer that you need, try replacing
    A positive answer will mean that the time spent was that much less than five; a negative answer shows how much more than five.

Posting Permissions

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