Excel Formula

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

Garry

Does anyone know how to do the following:

I want to format an employee timesheet where I can input
the start time and finish time (i.e. start time 15:30,
finish time 18:00) but there are two different rates of
pay within that time. I want to be able to show the
different rates of pay in different cells (i.e. 2 hours
at one rate and .5 hours at another rate. I think this is
an IF function.

Can anyone help?

Thanks
 
Garry said:
Does anyone know how to do the following:

I want to format an employee timesheet where I can input
the start time and finish time (i.e. start time 15:30,
finish time 18:00) but there are two different rates of
pay within that time. I want to be able to show the
different rates of pay in different cells (i.e. 2 hours
at one rate and .5 hours at another rate. I think this is
an IF function.

Can anyone help?

Thanks
Click to expand...

I will assume start time in A1 and finish time in B1.

Then the formula for normal time (i.e. up to 17:30) is
=MIN(B1-A1,TIME(17,30,0)-A1)

The formula for overtime is
=IF(B1>TIME(17,30,0),B1-TIME(17,30,0),0)

These formulas will give results in time format (02:00 and 00:30).
If you want the number of hours (2 and 0.5), multiply by 24 and format the
cells as general (or number):
=MIN(B1-A1,TIME(17,30,0)-A1)*24
=IF(B1>TIME(17,30,0),B1-TIME(17,30,0),0)*24
 
Back
Top