# Time Calculations Excel 2003

Discussion in 'Business Applications' started by kevb8ll, Jan 24, 2006.

Not open for further replies.

Joined:
Jan 10, 2002
Messages:
186
I URGENTLY need help on this I've got to complete it before lunch tomorrow GMT.

This is doing my head in!!

I want to display the difference of hours worked and hours budgeted.

Ok columns B = Hours worked C = Hours Budget D = Difference I've formatted a special with H:mm format.

The sum is obviously B-C

All is fine and dandy until The hours worked are less than than budet ie a negative number. Excel doesn't like negative time.

Easy I thought use an if and if the difference is less than zero do the sum the other way round ie C-B. Great I have a positive number which is correct - but they ALL look positive.

2 questions. How can I either:

1. Create a "negative" number so we can see the target wasn't met.

2. Format the results to be green positive and red font negative. Yes I tried conditional formatting and it didn't work.

Thanks.

2. ### Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
Hi there,

Assuming your formula is in D1, you could use this ..

=ABS(B1)-ABS(C1)

Use a Conditional Format (CF) such as (with D1 selected) ..

Formula Is:
=B1>C1

Format as desired for negative number.

Joined:
Jan 10, 2002
Messages:
186
What is the function ABS? Will it work with the time?

Joined:
Jan 10, 2002
Messages:
186
Didn't work - just returns ############ because it is a negative number.

The number HAS to be converted to a positive one first - but then I have to differentiate it!!

5. ### OBPTemporarily Banned

Joined:
Mar 8, 2005
Messages:
19,889
ABS stands for absolute and it should make it positive.

6. ### Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
You know what, you're right. My bad. I apologize for any confusion. This formula should get you there...

=ABS(B1-C1)

Joined:
Jan 10, 2002
Messages:
186
Thanks for your help I've sussed it using my original method - although you influenced me. I hadn't thought of using the conditional format and comparing the two sums.

1. Do the sum as described above - so I always get a positive. Then do a conditional format if cell is not equal to b2-b3 format text red. It will only not be the same because to get the positive I changed the sum around. The cells that are genuinely positive will format as green because they will = the sum.

Cheers

As Seen On