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

1. ## 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.....

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.

monique See attachment below....I forgot to attach it....sorry  Register To Reply

2. ## Re: More help with the IF function please

work book is attached above.............................thanks  Register To Reply

3. ## 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  Register To Reply

4. ## 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  Register To Reply

5. ## 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  Register To Reply

6. ## 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.

Thanks again... Monique  Register To Reply

7. ## 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.  Register To Reply

8. ## 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  Register To Reply

9. ## 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.  Register To Reply