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

2. Re: More help with the IF function please

work book is attached above.............................thanks

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

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

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

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

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.

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

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.

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

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