+ Reply to Thread
Results 1 to 9 of 9

Trying to work formula for work TIME SHEET using IF formula

  1. #1
    Registered User
    Join Date
    10-11-2009
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Smile Trying to work formula for work TIME SHEET using IF formula

    Hi there. I have another formula I would like help with (please) using the IF function. Well i think its the IF function.....

    Please see attachment.

    The total number of hours worked per day in cell C17. In this case its 16 hours.

    • The first 8 hours are paid at Normal Rate,
    • 8.01 to 9.99 hours are paid at 1.5x normal rate and....
    • 10 hours + are paid at double time.

    I would like to in cell C19 C19 and C20 have number of hours automatically put into these cells, as with each day of the week.

    I hope this is enough detail for you.

    Thanking you in advance.....

    monique

    See attachment below....I forgot to attach it....sorry
    Attached Files Attached Files
    Last edited by help_monique; 10-12-2009 at 09:44 PM.

  2. #2
    Registered User
    Join Date
    10-11-2009
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: More help with the IF function please

    work book is attached above.............................thanks
    Last edited by help_monique; 10-12-2009 at 09:44 PM.

  3. #3
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,363

    Re: More help with the IF function please

    Hi Monique,

    could you please come up with a more descriptive thread title? The forum rules explain why. Please take a moment to read them.

    As easily as you attached a JPG, you can attach a sample workbook. That'll make it much easier to see your data structure and which existing formulae you already have in place. A spreadsheet is worth 1000 pictures.

    You'll get an answer soon once you've sorted these two issues.

    cheers

  4. #4
    Registered User
    Join Date
    10-11-2009
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Trying to work formula for work time sheet using IF formula

    i Teylyn,

    Thanks for the info. I have now attached a work book, hopefully that helps...thanks

  5. #5
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,363

    Re: Trying to work formula for work TIME SHEET using IF formula

    Hi,

    in C18 you need

    =IF(C17>8,8,C17) -- If the total is more than 8 hrs, return 8, otherwise return the total

    in C19 you need

    =IF(C17>10,2,C17-8) -- If the total is more than 10 hrs, return 2, otherwise return the total minus 8 hrs = the hours on 1.5 x Normal rate. This will return 0 if the total is 8 hours and will return any time worked above 8 hrs but no more than 2

    and in C20 you need

    =IF(C17>10,C17-10,0) -- If the total is more than 10 hrs, return the total minus the 10 hrs already covered in the previous cells. If the total is less than 10 hrs, this cell will be 0

    hth

  6. #6
    Registered User
    Join Date
    10-11-2009
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Trying to work formula for work TIME SHEET using IF formula

    Thank you so much for this.....it will save me so much time each week during payrol time. I have one more question though.

    In the time and a half row (10 E & F)when there are no hours at this 1.5 rate it is reading minus 8 but should be zero. Ive tried playing around with it but can not get it to 0.

    Please see attached...

    Thanks again... Monique
    Attached Files Attached Files

  7. #7
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,363

    Re: Trying to work formula for work TIME SHEET using IF formula

    Hi,

    change the formula to

    =IF(B8>10,2,IF(B8=0,0,B8-8))

    and copy right. I put in another IF statement for cases where the total time is not greater than 10. If the total time equals 0 it will return a 0, otherwise it will return the total -8.

  8. #8
    Registered User
    Join Date
    10-11-2009
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Trying to work formula for work TIME SHEET using IF formula

    Hi Again,

    Im really really sorry. I have come across another problem. Sometimes the guys work less than 8 hours per day so therefore the 1.5 rate of pay should still equal zero. I know i need to put more IF statements for .25 to 7.75 hours, but i dont know how to...... See attached work sheet

    Maybe this is getting too complex and can not be done. My apologies once again....

    Thanks Monique
    Attached Files Attached Files

  9. #9
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,363

    Re: Trying to work formula for work TIME SHEET using IF formula

    Ok, no problem. Change row 19 to

    =IF(B17>10,2,IF(B17<8,0,B17-8))

    and copy right.

+ 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