hours x rate calculation

  • Thread starter Thread starter martin
  • Start date Start date
M

martin

I have a spreadsheet with two columns detailing hours worked, (i.e. from and
to) and I have then two columns that convert the hours into a number format,
and then a column to take one away from the other to give total hours
worked. Depending on the start and finish times, I need to multiply the
hours worked by a rate of either single rate, rate and a half or double
rate.
For example if a person works from 10.00 am until 18.00pm the hours worked
from 10.00 until 16.00 will be at single rate whereas the hours from 16.00
to 18.00 will be at rate and a half. Similarly if a person works from 04.00
to 14.00 the first 2 hours will be at double rate and the next 8 hours will
be at single rate. The double rate also relates to hours worked after
midnight until 6.00am.
Can anyone suggets an easy way to make the calculation, or do i need to
write some code?

Many thanks in anticipation
Martin
 
Will this work?

Assume cell D20 contains the numeric value of the start time (in days)

Assume cell E20 contains the numeric value of the end time (in days)


=(ABS(6/24-D20)*2)+((IF(E20>=16/24,16/24,E20)-IF(D20<=6/24,6/24,D20))*1)+(IF(E20>16/24,IF(E20<24/24,E20-16/24,24/24-16/24),0)*1.5)+(IF(E20>24/24,E20-24/24,0)*2)

This will return the total pay in days.

For everything in hours try this:

=(ABS(6-D21)*2)+((IF(E21>=16,16,E21)-IF(D21<=6,6,D21))*1)+(IF(E21>16,IF(E21<24,E21-16,24-16),0)*1.5)+(IF(E21>24,E21-24,0)*2)

I noticed if someone works 6 am to 4 pm they get paid for 10 straigh
hours by this equation.... is that correct
 
Back
Top