Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 16
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 793
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
overtime hours formula Louie Excel Worksheet Functions 8 December 16th 07 10:41 PM
if then formula for total time ;overtime hours versus regular OTVs Regular Excel Worksheet Functions 1 September 13th 07 08:24 AM
Overtime Hours Zack Excel Worksheet Functions 5 November 23rd 05 05:28 PM
How to compute overtime pay Boro New Users to Excel 1 October 31st 05 10:20 AM
need help w/formula for calculating overtime hours jv749297 Excel Worksheet Functions 1 January 17th 05 08:54 PM


All times are GMT +1. The time now is 06:03 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"