Variable Pay-rate after 5pm
 

After 5pm employees get paid a different rate per hour.
Currently 4 entries for time "in" and "out" before and after lunch.

Need to have Excel determine that after lunch, say, "1:45pm"-"6:15pm"
includes 1.25 hour AFTER 5pm and needs to apply a different pay rate to that
1.25 hrs.

I really don't want to have to add an extra 3 cells to the equation
from"Clock in from lunch" to "4:59 PM" and "5:00 PM" to "Clock out for the
day".

Office 2007 Ulitmate Excel
Win7 64bit

Variable Pay-rate after 5pm
 

Assume

A1= normal hourly rate
A2= overtime hourly rate

A3= startting time
B3= gone to lunch time
C3= restart time
D3=finish time

=((B3-A3+D3-C3)*$A$1+MAX(D3-0.708333,0)*($A$2-$A$1))*24

or in plain language
all worked hours at normal rate + any time after 17:00 at the extra rate
(A2-A1)
*24 because the time is in part of a day.
0.708333 is the equivalent of 17:00

If you have different payrates per person than you will need to have two
seperate columns for them. say column E and F
and the formula would be
=((B3-A3+D3-C3)*E3+MAX(D3-0.708333,0)*(F3-E3))*24

Greetings from New Zealand

"SchMattX" wrote in message
...
After 5pm employees get paid a different rate per hour.
Currently 4 entries for time "in" and "out" before and after lunch.

Need to have Excel determine that after lunch, say, "1:45pm"-"6:15pm"
includes 1.25 hour AFTER 5pm and needs to apply a different pay rate to
that
1.25 hrs.

I really don't want to have to add an extra 3 cells to the equation
from"Clock in from lunch" to "4:59 PM" and "5:00 PM" to "Clock out for the
day".

Office 2007 Ulitmate Excel
Win7 64bit