IF formula

  • Thread starter Thread starter Garry
  • Start date Start date
G

Garry

Does anyone know the formula to use if I want to do an
employee timesheet where two different rates of pay apply
to the hours they work.

For example:If an employee starts at 15:30 and finishes
at 18:00 one rate of pay applies up to 17:30 and another
applies from 17:30 to 18:00.

I want a to be able to show this in two different cells
i.e. one cell showing 2hrs at Ordinary rate and another
cell showing .5 hours at PM rate.

How do I do this?

Thanks in advance
 
Start time in A1, end time in B1

for the first 2 hours

=MIN(B1-A1,--"02:00")*24*payrate_for_first_2_hours

for anything above 2 hours

=MAX(B1-A1-"02:00",0)*24*payrate_for_more_than_2_hours

so if the first 2 hours were paid 20 dollars per hour and above 2 hours 30
dollars per hour the formulas would look like
this

=MIN(B1-A1,--"02:00")*24*20

and

=MAX(B1-A1-"02:00",0)*24*30

format result as currency, maybe round to nearest cent

=ROUND(MIN(B1-A1,--"02:00")*24*20,2)
 
Back
Top