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

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

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

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

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.

Ok, no problem. Change row 19 to

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

and copy right.