1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Time Calculations Excel 2003

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

Thread Status:
Not open for further replies.
Advertisement
  1. kevb8ll

    kevb8ll Thread Starter

    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

    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.
     
  3. kevb8ll

    kevb8ll Thread Starter

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

    kevb8ll Thread Starter

    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. OBP

    OBP Temporarily Banned

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

    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)
     
  7. kevb8ll

    kevb8ll Thread Starter

    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
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/436856

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice