Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating a daily rate when the rate could be fixed or variable | New Users to Excel | |||
Variable interest rate | Excel Worksheet Functions | |||
How do I calcualte APR on Variable Rate Mortgages? | Excel Worksheet Functions | |||
Twist on the variable rate problem | Excel Worksheet Functions | |||
Possible Variable in Rate Function | Excel Worksheet Functions |