If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Labor Cost for Date Range
I'm designing a query that needs to figure out the labor cost for a certain time period based on hours worked. There is a regular pay rate and an OT pay rate. Right now I'm grouping on weeks (by week #'s). The query is based on start and end dates. If the date range starts in the middle of the week, it will not be able to sum up all the hours worked in that week, which will affect the labor cost of that week, since anything over 40 is multiplied by the OT pay rate.
Does anyone have any suggestions on how to get moving in the right direction on this? Thanks, DocX --------------= Posted using GrabIt =---------------- ------= Binary Usenet downloading made easy =--------- -= Get GrabIt for free from http://www.shemes.com/ =- x-- 100 Proof News - http://www.100ProofNews.com x-- 3,500+ Binary NewsGroups, and over 90,000 other groups x-- Access to over 1 Terabyte per Day - $8.95/Month x-- UNLIMITED DOWNLOAD |
#2
|
|||
|
|||
On Sat, 1 Jan 2005 16:24:27 -0500, "AnonY"
wrote: I'm designing a query that needs to figure out the labor cost for a certain time period based on hours worked. There is a regular pay rate and an OT pay rate. Right now I'm grouping on weeks (by week #'s). The query is based on start and end dates. If the date range starts in the middle of the week, it will not be able to sum up all the hours worked in that week, which will affect the labor cost of that week, since anything over 40 is multiplied by the OT pay rate. Does anyone have any suggestions on how to get moving in the right direction on this? Don't store any grouping in tables at all, would be my recommendation. At the least, store each day's hours in a separate record. You can then create a Totals query to sum the hours. You can calculate the total with overtime with an expression like [PayRate] * IIF(Sum([Hours]) = 40, Sum([Hours]), 40) + [OTRate] * (IIF(Sum([Hours]) 40, Sum([Hours]) - 40, 0) John W. Vinson[MVP] |
#3
|
|||
|
|||
You may want to add a field into the table for the rate of pay. Some
companies will pay for any work done past 8 hours in a day (depending on if your state hasn't changed the labor law - I think where I am in WA state, the state changed the law to favor the companies so they only have to pay for hours above 40 in a week, where I was in CA, they would pay above 8 hours in a day). Anyways, store the hours work in a table, where the record is based on the day worked, the amt of horus, then the "rate" (this is not the employees hourly rate, since that should be stored in the employee table), but rather, this would be 1 or 1.5 (time and a half) or even 2 (for double time). Then you query can take the amt of hrs worked, times the employees pay rate, times the "daily rate" - obviously this "daily rate" field would have to be filled in my HR or the employees mngr, or you could automate its entry in a forms "before update" event, if this app has a time card type functionality. "AnonY" wrote: I'm designing a query that needs to figure out the labor cost for a certain time period based on hours worked. There is a regular pay rate and an OT pay rate. Right now I'm grouping on weeks (by week #'s). The query is based on start and end dates. If the date range starts in the middle of the week, it will not be able to sum up all the hours worked in that week, which will affect the labor cost of that week, since anything over 40 is multiplied by the OT pay rate. Does anyone have any suggestions on how to get moving in the right direction on this? Thanks, DocX --------------= Posted using GrabIt =---------------- ------= Binary Usenet downloading made easy =--------- -= Get GrabIt for free from http://www.shemes.com/ =- x-- 100 Proof News - http://www.100ProofNews.com x-- 3,500+ Binary NewsGroups, and over 90,000 other groups x-- Access to over 1 Terabyte per Day - $8.95/Month x-- UNLIMITED DOWNLOAD |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Formula to Count and Return Most common Value in a Dynamic Named Range | Tinä | General Discussion | 1 | October 23rd, 2004 11:26 PM |
Formulas with named ranges with 2 or more range areas | agarwaldvk | Worksheet Functions | 1 | September 7th, 2004 07:33 AM |
Printing named range only | ray | Worksheet Functions | 4 | April 26th, 2004 05:29 PM |
is there a formula that can count a range of cells with text? | Frank Kabel | Worksheet Functions | 0 | March 11th, 2004 08:04 PM |
Question about an argument, in an OFFSET dynamic range formula | Terry B. | Worksheet Functions | 6 | December 10th, 2003 10:53 PM |