Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to Compute Overtime hours
I have a document which is set up like a calendar. It is used to calculate
an employee's work hours per month. It is also used to calculate any additional duties they work during work days or on days off. This document is already set up for these calculations. I now want it to tell me, on work days only, how much time an employee spends working particular duties when it exceeds their normal work hours (this is chosen by the user using a drop down list to choose their appropriate work hours - could be between 8 and 12 hours). I'm looking to capture this information for a month. Example: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 (this indicates the days of the month) w w w w w (w=workday, blank=day off) 12 3 4 13 2 1 10 7 (number of hours working each DOM) Normal work hours: 9 (user input from drop down) In this example, this employee's normal work schedule is 9 hours a day. I am looking for a formula which will add the overage (3 hours for the 1st and 4 hours for the 6th). I don't want to capture any amount which is equal or less than 9 hours. However, if the user were to use the drop down to show their hourly day as 10 hours, I would be looking to capture the same information as shown above (2 hours for the 1st and 3 hours for the 6th). Thanks very much in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to Compute Overtime hours
Assuming the value the employee chosen is stored in A7
[A1-AE1 will contain dates, A2-AE2 will contain "w" or blank, A3-AE3 will contain the actual hours worked Then enter this in AF3 and press CTRL-SHIFT-ENTER together... =SUMPRODUCT(--(A2:AE2="w"),--(A3:AE3A7),A3:AE3-A7) to get the total of hours greater than the value in A7... "Lee Ann" wrote: I have a document which is set up like a calendar. It is used to calculate an employee's work hours per month. It is also used to calculate any additional duties they work during work days or on days off. This document is already set up for these calculations. I now want it to tell me, on work days only, how much time an employee spends working particular duties when it exceeds their normal work hours (this is chosen by the user using a drop down list to choose their appropriate work hours - could be between 8 and 12 hours). I'm looking to capture this information for a month. Example: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 (this indicates the days of the month) w w w w w (w=workday, blank=day off) 12 3 4 13 2 1 10 7 (number of hours working each DOM) Normal work hours: 9 (user input from drop down) In this example, this employee's normal work schedule is 9 hours a day. I am looking for a formula which will add the overage (3 hours for the 1st and 4 hours for the 6th). I don't want to capture any amount which is equal or less than 9 hours. However, if the user were to use the drop down to show their hourly day as 10 hours, I would be looking to capture the same information as shown above (2 hours for the 1st and 3 hours for the 6th). Thanks very much in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
overtime hours formula | Excel Worksheet Functions | |||
if then formula for total time ;overtime hours versus regular | Excel Worksheet Functions | |||
Overtime Hours | Excel Worksheet Functions | |||
How to compute overtime pay | New Users to Excel | |||
need help w/formula for calculating overtime hours | Excel Worksheet Functions |