1. Registered User
Join Date
Dec 2007
Posts
72

Hi,

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

OB1

2. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445

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

3. Registered User
Join Date
Oct 2003
Posts
1,091
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

4. Registered User
Join Date
Dec 2007
Posts
72
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. Grumpy old man (training)
Join Date
Sep 2006
Location
Surrey, UK
Posts
1,102
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
Code:
`=(C2-B2)`
with
Code:
`=5-(C2-B2)`
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
•