+ Reply to Thread
Results 1 to 10 of 10

Calculating Time Owed based on Time Worked

  1. #1
    Forum Contributor
    Join Date
    06-18-2008
    Posts
    123

    Calculating Time Owed based on Time Worked

    Hi all.

    I know the title is a bit vague, but I cant think how else to word it!

    I have a sheet (attached) which works out hours worked, and if the amount is under a specified target, it counts how much time is owed. The problem occurs when someone works more hours than the specified target.

    I guess I need an IF formula of some kind, to say if the figure is over the target, to put zero in the hours owed column.

    Any ideas?

    Thanks,

    Tony
    Attached Files Attached Files
    Last edited by Tony Vargo; 06-23-2009 at 11:30 AM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Help with IF formula

    What should the result be?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Calculating Time Owed based on Time Worked

    If you want 0 then it would make sense to use MAX (the problem is that XL won't show negative time (give or take some tweaking))

    =MAX(0,SUM(A4-(B4+C4)))

  4. #4
    Forum Contributor
    Join Date
    06-18-2008
    Posts
    123

    Re: Calculating Time Owed based on Time Worked

    Hi NBVC

    If the time worked is over 7:30, I want the result to be zero.

    Thanks

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Calculating Time Owed based on Time Worked

    Then you probably want to see DonkeyOte's solution (which is what I was going to suggest based on your answer)..

    Although, don't think you need SUM() function...

    =MAX(0,A4-(B4+C4))

    or

    =MAX(0,A4-B4-C4)

  6. #6
    Forum Contributor
    Join Date
    06-18-2008
    Posts
    123

    Re: Calculating Time Owed based on Time Worked

    DonkeyOte that works perfectly! Thanks a lot

  7. #7
    Forum Contributor
    Join Date
    06-18-2008
    Posts
    123

    Re: Calculating Time Owed based on Time Worked

    Sorry guys, theres more!

    I just remembered that if someone isnt working one day, the worked column will be blank and the formula result says they owe 7:30

    Is there anything we can add to the formula to say if column B is empty, to return zero?

    Thanks a lot,

    Tony

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Calculating Time Owed based on Time Worked

    You could embed an IF or alternatively given small no. of calcs being performed...

    =MAX(0,A4-B4-C4)*(B4<>0)

  9. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Calculating Time Owed based on Time Worked

    Quote Originally Posted by Tony Vargo View Post
    Sorry guys, theres more!

    I just remembered that if someone isnt working one day, the worked column will be blank and the formula result says they owe 7:30

    Is there anything we can add to the formula to say if column B is empty, to return zero?

    Thanks a lot,

    Tony
    Hence why I asked questions first in post #2..

  10. #10
    Forum Contributor
    Join Date
    06-18-2008
    Posts
    123

    Re: Calculating Time Owed based on Time Worked

    Awesome, works now!

    Thanks for all your help

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1