complex overtime/late shift compensation calculations

I'm running a 24 hour store and the calculation of staff hours drives
me insane.
I have employees that work by the hour, in full 100% and in part time
%. The employees that does not work by the hour have fixed work-times
on a schedule so that the hours will add up to 39*52*percentage of
full time employment hours for a year. Some weeks a person might work
many hours and are compensated for it the next week when they work
fewer hours.

First the staff gets compensation for working late:
Week-days
50% on every minute between 17:00-21:00 weekdays
100% on every minute between 21:00-4:00 weekdays
50% on every minute between 4:00-8:00 weekdays
Normal hours between 8:00-17:00

Saturdays:
Normal hours between 8:00-13:00
50% on every minute between 13:00-21:00 Saturdays
100% on every minute between 21:00-4:00 Saturdays
50% on every minute 4:00-8:00 Saturdays

Sundays
50% on every minute 4:00-21:00
100%on every minute between 21:00-4:00

Eves (like Christmas eve)
50% on every minute between 4:00-13:00
100% on every minute between 13:00-4:00

Holidays
100% on every minute the whole day

Conditions for overtime
Also the staff gets overtime if they work more than 39 hours a week or
more than 9 hours a day if the change is initiated by the employer.
They will not get double compensations, they will get either
compensation for working late or overtime, whichever is the highest.

Part-time employees
 For any extra shifts initiated by the employer in addition to the
shifts on the full year plan up to an added 39 hours for the week
there is an addition of 30% for every minute between 8-17 on weekdays
and 8-13 Saturdays, and 100% after 17:00 on weekdays and after 13:00
on Saturdays.

For full time employees, part time employees that has worked 39 hours
in a week
Weekdays 8-21 is compensated with 50% extra
Weekdays 21-24 is compensated with 100% extra
Saturdays 8-13 is compensated 50% extra
Saturdays 13-24 is compensated 100% extra
Sundays is compensated 100%extra the whole day

So...
For example if i tell a person to change times from an evening shift
to a day-shift in a week on a weekday where the employee works 39
hours already, the employee should get overtime compensation for the
CHANGE from the previous agreed upon time that surpasses 39 hours or 9
hours a day.
The employee was supposed to work 16:00-22:00 (6hrs) and i tell her to
work 12:00-19:00 (7hrs) the employee should get 1 hour overtime ( 50%
extra) in the period between 12:00-16:00.  This is important since the
employee will get compensation for working late after 17:00. This
employee should get 1 hour overtime compensation somewhere in the
period 12-16 and 50% compensation for working late in the period
17-19.

If the employee will get an unscheduled free day later in the week,
there will be no overtime for this day as the summed up hours for the
week will not surpass 39 hours.

Also, overtime should not me a factor if two employees agree to change
work-times with each other. It should only be a factor when initiated
by the employer!

Any thoughts on how to create a book that takes all these variables
into effect?

I have constructed a book that will calculate the compensations for
working late but the overtime is killing me.
0
oslopelle (6)
4/6/2008 2:54:18 PM
excel 39879 articles. 2 followers. Follow

2 Replies
553 Views

Similar Articles

[PageSpeed] 10

Dea Oslo Pelle

I would actually write a VBA macro to deal with this and use some rule based 
system to pick up all the exceptions. For example the situation when two 
staff agree to switch is difficult to program and checking to ensure that 
they comply. Also the problem of OT in those situations is complex. But 
basically you need to build up some method of documenting the rules so that 
you can just enter the times in the computer and get the pay out at the end. 

Personally payroll tends to lend itself to more traditional programming or 
database work than excel. 

But good luck.
-- 
Hope this helps
Martin Fishlock, Bangkok, Thailand
www.nysconsultants.com
Please do not forget to rate this reply.


"Oslopelle" wrote:

> I'm running a 24 hour store and the calculation of staff hours drives
> me insane.
> I have employees that work by the hour, in full 100% and in part time
> %. The employees that does not work by the hour have fixed work-times
> on a schedule so that the hours will add up to 39*52*percentage of
> full time employment hours for a year. Some weeks a person might work
> many hours and are compensated for it the next week when they work
> fewer hours.
> 
> First the staff gets compensation for working late:
> Week-days
> 50% on every minute between 17:00-21:00 weekdays
> 100% on every minute between 21:00-4:00 weekdays
> 50% on every minute between 4:00-8:00 weekdays
> Normal hours between 8:00-17:00
> 
> Saturdays:
> Normal hours between 8:00-13:00
> 50% on every minute between 13:00-21:00 Saturdays
> 100% on every minute between 21:00-4:00 Saturdays
> 50% on every minute 4:00-8:00 Saturdays
> 
> Sundays
> 50% on every minute 4:00-21:00
> 100%on every minute between 21:00-4:00
> 
> Eves (like Christmas eve)
> 50% on every minute between 4:00-13:00
> 100% on every minute between 13:00-4:00
> 
> Holidays
> 100% on every minute the whole day
> 
> Conditions for overtime
> Also the staff gets overtime if they work more than 39 hours a week or
> more than 9 hours a day if the change is initiated by the employer.
> They will not get double compensations, they will get either
> compensation for working late or overtime, whichever is the highest.
> 
> Part-time employees
>  For any extra shifts initiated by the employer in addition to the
> shifts on the full year plan up to an added 39 hours for the week
> there is an addition of 30% for every minute between 8-17 on weekdays
> and 8-13 Saturdays, and 100% after 17:00 on weekdays and after 13:00
> on Saturdays.
> 
> For full time employees, part time employees that has worked 39 hours
> in a week
> Weekdays 8-21 is compensated with 50% extra
> Weekdays 21-24 is compensated with 100% extra
> Saturdays 8-13 is compensated 50% extra
> Saturdays 13-24 is compensated 100% extra
> Sundays is compensated 100%extra the whole day
> 
> So...
> For example if i tell a person to change times from an evening shift
> to a day-shift in a week on a weekday where the employee works 39
> hours already, the employee should get overtime compensation for the
> CHANGE from the previous agreed upon time that surpasses 39 hours or 9
> hours a day.
> The employee was supposed to work 16:00-22:00 (6hrs) and i tell her to
> work 12:00-19:00 (7hrs) the employee should get 1 hour overtime ( 50%
> extra) in the period between 12:00-16:00.  This is important since the
> employee will get compensation for working late after 17:00. This
> employee should get 1 hour overtime compensation somewhere in the
> period 12-16 and 50% compensation for working late in the period
> 17-19.
> 
> If the employee will get an unscheduled free day later in the week,
> there will be no overtime for this day as the summed up hours for the
> week will not surpass 39 hours.
> 
> Also, overtime should not me a factor if two employees agree to change
> work-times with each other. It should only be a factor when initiated
> by the employer!
> 
> Any thoughts on how to create a book that takes all these variables
> into effect?
> 
> I have constructed a book that will calculate the compensations for
> working late but the overtime is killing me.
> 
0
4/6/2008 3:54:01 PM
You might adapt this to your needs:
http://www.freefilehosting.net/download/3f0f4
0
4/7/2008 5:39:38 PM
Reply:

Similar Artilces:

Locking a Cell after It's Value is Calculated.
I have One Cell whose value is constantly changing once a week. The results of that cell, I would like to have placed in differents cells, once a week. EX: In worksheet1 Input in cell WK3, resulting calculation ends in cell M3, then cell M3 is copied to worksheet2, A1 (=worksheet1M3) I got this! Input in cell WL3, resulting calculation ends in cell M3, then M3 is copied to worksheet2, A2: The problem is that cell A1 changes to the new number HELP! Can I lock cell A1 after the value is copied to it? Can I set conditions (time / date) on the cell after the value i copied to it so it won...

Want to make complex header on 2nd and 3rd page
I need to make a quotation which consists of a first page, thats fully on its own and then two pages which has a same header. The header, though, consists of quite a lot of text and also a company logo and that was byond the header functionality. How to get the 2nd and 3rd pages with a complex upper part? Bart Exc 2003 ...

Calculating frequencies
I'm frustrated (or maybe embarrassed) to have to ask this, but I can't find in Help or a MS Office Pro 2000 book how to calculate the frequency of unique values (zipcodes) listed in a single column, let alone put the resultant list in another column (or anywhere empty). I'd be grateful if someone would point the way. This is Excel 2000. Thanks. Gene Hi! Use an advanced filter to copy the uniques to another column then use Countif to get the frequency. See if you can figure it out from here. If you still need assistance just post back and I (or someone) will be glad to give yo...

calculate 150% of a value
Hi all.. im a bit of a newbie .. can anyone help me with this? Im making a spreadsheet that has a dollar amount in the second column. The third column has to calculate 150 % of the the value from the second column. I have to calculate this for each row in the column. How can I do this? Can someone point me in the right direction? Thanks! Type this in C1: =B1*1.5 And using the little box on the bottom right corner of that cell outliner, drag down the column. You could double click on that "autofill" button and it'll go until it finds a gap in the adjacent column. Anot...

Calculating a grand total for an unbound control in a report
I have a report for sales grouped by customer as follows customerA itemA lbs sold $ sales itemB lbs sold $ sales customerB itemC lbs sold $ sales etc..... The report automatically subtotals per customer for lbs and $ then grand totals in the report footer. We have some customers with rebates, returns, allowances etc... that we have an adjustment to the $ sales # that I pull in on the query that is the source for this report and it shows up under the customer subtotal then I have a control box that calcul...

how to access cell calculator #2
There is a way to cause a small calculator to appear when you click on a spreadsheet cell. How do I activate this feature? I can not find it anywhere and can find no info on it. From the VBA help file... 'This example uses the Shell function to run the Calculator application 'included with Microsoft Windows. 'It uses the SendKeys statement to send keystrokes to add some numbers, 'and then quit the Calculator. 'The SendKeys statement is not available on the Macintosh. '(To see the example, paste it into a procedure, then run the procedure. 'Because AppActivate ch...

partially turning off auto calculate?
Any way to NOT auto caluculate 1 particular formula, while leaving the rest of excel to auto calculate? In particular, I have an SQL query I would prefer to run manually, while not interferring with the rest of my workbook(s) ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Calculation is an all or nothing setting. You can't prevent calculation of specific ranges. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com ...

Help creating a script in SQL or Calculated field in Crystal
How do I take this script and manipulate it to give me one long string with static text as well as SQL data in specific positions within the string. Example result: Positions / Data: 1-3 / 173 (Static text) 4-6 / spaces 7-10 / "X_UPR30300"."YEAR1", (has to show up as 2007. Showing up as 2,007) 11 / 4 (Static text) 12-22 / "UPR00100"."SOCSCNUM" 23-57 / "UPR00100"."LASTNAME" 58-92 / "UPR00100"."FRSTNAME" 93 / "UPR00100"."MIDLNAME" 94-120 / "UPR00102"."ADDRESS1" 121-148 / &q...

Resetting cell value; after time/calculation has been made; Resolving circular ref.
Im very new to excel 97 and have two questions/problems; 1. Can a cell be formatted to reset to a "0" value after a specifie time or after it's value has been used/calculated? 2. I want to solve the following senerio; cell a1 = an inserted value added to sum of a3; then resets to after...3-5 seconds OR after A3l value has been calculated. cell a2= an inserted value subtracted from sum of a3; then resets to after...3-5 seconds OR after A3 value has been calculated. cell a3=current balance. (A1/A2 use this value to add/sub from; hence circular reference. I'm trying to ...

shift and down keys in macro
Hi, I want to mark four cells. How can I write shift+down+down+down in a macro. (Then I say copy paste etc). Thanks Cousin Excel Activecell.Resize(4).Select Activecell.Resize(4).Copy OR starting from cell A1 copy 4 cells to the same sheet cell J1 Range("A1").Resize(4).Copy Range("J1") -- Jacob "CousinExcel" wrote: > Hi, > I want to mark four cells. > How can I write shift+down+down+down in a macro. > (Then I say copy paste etc). > Thanks > Cousin Excel Hi and thank you Jacob Skaria. This was something new for me...

Calculations in Time Problem
im sure this is obvious but say ive got 2 times A1)1:32.101 A2)1:32.105 how can i do the maths A2-A1 etc , ive formatted everything t mm:ss.000 as ive read that when searching on forum but when i put thi in B1 =A2-A1 i just get #VALUE how do i get it so it gives me the answer 0:00.004 in B1 any help would be much appreciated: -- scottymellot ----------------------------------------------------------------------- scottymelloty's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=380 View this thread: http://www.excelforum.com/showthread.php?threadid=48882 Sound...

Calculating totals by month from whole date
I'm trying to figure out the proper calculation to pull totals by dat in Excel. Here's my problem: I have a workbook with 2 worksheets; one is the main data, the other i the statistics from the main data. Within the main data, there is column titled "date paid" and another column titled "total paid" (ther are a bunch more columns, but they don't matter for this problem). Th "date paid" column will contain dates such as 1/1/04, 5/15/04 etc. O the stats page, I have columns titled for each month of the year an would like each to include how much was pa...

Auto calculate
I am trying to find a formula that automatically calculates multiples of 32. if it is not a multiple of 32 then I wnat he cell to be blank. Any suggestions? if page count =32 then sig should = 1 if page count = 64 then sig should = 2 and so on.... See spreadsheetsample below. I manually have to fill in the 'sig' column with the number. Thanks, barb # of pages Page count Sig 2 2 2 4 2 6 2 8 2 10 2 12 2 14 2 16 2 18 2 20 2 22 2 24 2 26 2 28 2 30 2 32 1 2 34 2 36 2 38 2 40 2 42 2 44 2 46 2 48 2 50 2 52...

Automatic calculation reverting to manual depending upon who opens
The Accounts team just produced a Board report on our financial situation. An email was sent out with an excel sheet attached. Both I and a colleague opened the file direct from Outlook and both saw significantly different results reported. His version of Excel had switched the Calculation tab (Tools/Options menu) to Manual - mine however remained as Automatic and reported the correct numbers. Despite having the same formulas in the same cells his was not summing the equations correctly. Why is this? Not too hard to explain, if your colleague opened any workbook with calculation ...

Calculated field in pivot table #2
Hi I have a data source like this ITEM QTY LOCATION GGG 1,500 STORE-A GGG 1,200 STORE-B TTT 37,000 STORE-A TTT 39,000 STORE-B and futher make a pivot table like this Sum of QTY LOCATION ITEM STORE-A STORE-B GGG 1,500 1,200 TTT 37,000 39,000 Grand Total 38,500 40,200 The question is I want to make a "calculated field" in the pivot table to make the diff like Sum of QTY LOCATION ITEM STORE-A STORE-B DIFF GGG 1,500 1,200 -300 TTT 37,000 39,000 2,000 Grand T...

Function Won't Calculate -- Sometimes
I have a very simple function I created which is reproduced below. It just starts from a specified cell and searchs down the column to find the first number (as opposed to text or NA or something) and returns the offset to that cell from the original one. Function FirstNumberOffset(TopCell) RowOffset = 0 While Not (IsNumeric(TopCell.Offset(RowOffset, 0))) RowOffset = RowOffset + 1 Wend FirstNumberOffset = RowOffset End Function I call it in the spread sheet as: [ ] =FirstNumberOffset(C8) I wrote it, and it worked as expected whi...

Leave out tax sheltered annuities in workers compensation report
We are a government entity and do not report tax shetered annuties on our Workers Compensation Report. We use Great Plains 8.0 and are in the process of upgrading to 9.0. We would like to see a button to choose what taxes to include on our Workers Compensation Report. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and t...

Modify Calculation
When a user enters data into a cell, he has to tab out of it before he can push the "calculate" button. Is there any way to change this so that the calculation can be performed while the cell is still selected? I appreciate any help. Change Tools>Options>Edit>Move selection after enter, and use Enter to complete entry rather than Tab. This prevents the selection from moving to another cell. Note that calculation can never be done while the cell is being edited. "Sisilla" <sookdeoss@bowater.com> wrote in message news:0b4e01c34b9b$d59ebcf0$a301280a@p...

multiple calculations in 1 cell
In cell C 13 is the quantity of units. In cell G 13 is the total cost. In cell G 6 is the percent of overhead and in cell G 8 is the percent of profit. I want to enter a formula in cell H 13 that will give me a unit price which is (cost plus overhead) + profit. Try this: =3D(G13*(1+G6)*(1+G8))/C13 Hope this helps. Pete On Mar 5, 9:10=A0pm, Pulling Hair <PullingH...@discussions.microsoft.com> wrote: > In cell C 13 is the quantity of units. =A0In cell G 13 is the total cost.= =A0In > cell G 6 is the percent of overhead and in cell G 8 is the percent of pro...

Calculating with Textboxes ...
Dear all; I have a UserForm with a few textboxes. The first textbox : tbAmount holds an invoice amount. The second textbox: tbVAT should display the amount of VAT ( 19 % of the amount ) The formula I use is: .tbVAT.Value = .tbAmount.Value * 0.19 Looks fine to me. But, the stupid box don't disply the right amount. I live in Europe and the Regional settings are a dot ( . ) for the thousands seporator and a comma ( , ) for the decimal seporator. When I enter 2250,00 ( or 2250 ) in .tbAmount, then textbox .tbVat displays : 427.5 which is not what I want as I ho...

How to delete a range and shift the rest up in Vbasic?
Selection.Delete Shift:=xlUp This needs a selection first. The macro recorder can be your friend. Try it -- Don Guillett SalesAid Software donaldb@281.com "serdar" <serdarsoy@yahoo.com> wrote in message news:ObI6QUobFHA.2736@TK2MSFTNGP12.phx.gbl... > Selection.Delete Shift:=xlUp > > This needs a selection first. > > nope. i creates this code: Selection.Delete Shift:=xlUp i want to delete lets say a1 to c3. i can do it with select but i dont want selection is shown to the user before deleting the area. i must say smt like "shift the list up starti...

Excel 2007
How do I calculate what percentage of a year has elapsed? I have the date 31/12/09 in cell A1 and the date 31/05/09 in cell B1 and then I do this: =B1/A1*100 but this formula is wrong.How should I do the calculation? Thanks, John =1+(B1-A1)/365 If you want to account for leap years, use a third cell with 31-12-2008. If this is in A2, use this formula: =1+(B1-A1)/(A1-A2) -- Kind regards, Niek Otten Microsoft MVP - Excel "john" <john@discussions.microsoft.com> wrote in message news:6F1E94F8-3E3D-4361-BB77-A8E0231FC52F@microsoft.com... > How do I calculate what per...

Subform Calculations
I am using a tab-control continuous subform to enter multiple part numbers and quantity for a manufacturing process. Each quantity entered must be increased per a "Add-On %" field, which is a combo box in the subform listing multiple %'s. A SetValue macro calculates the entered [Quantity * (1 + the Add-On) to produce a "Total Quantity" for each entry. The problem is that the calculation only gets applied to the first entry in the subform, and ignores the rest of the list. Your help will be appreciated. ...

calculating dates #2
What formula do i use??? here is my problem... i have 3 columns A- Qty of days eg: 6, B-start date, eg: 10/12/07 C -end date, , (formula i am using, (colummn (C) =A+B+1 (copied down the column) this gives me an end date by adding A and B which i use in a gantt chart, eg; "but" some dates do not have a start date yet and the end result will read (C) 05/01/00, how can i get column (C) to remain blank until i put a start date in???? =IF(A1="","",A1+B1+1) -- Kind regards, Niek Otten Microsoft MVP - Excel "spudpeeps" <spudpeeps@discussions.mic...

calculating value in a query
how would i build a function that would calculate value which is basically aggregate summary per group ? Here is an example: Table Demand Item Qty Date aaa 10 03-mar-2010 aaa 7 05-mar-2010 aaa 5 12-mar-2010 ccc 8 06-mar-2010 ccc 12 09-mar-2010 Table On Hand Item Qty aaa 12 bbb 9 ccc 6 the result should be table Net Demand Item Qty Date (how to get column Qty) aaa 0 03-mar-2010 (12-10, left 2) aaa 5 05-mar-20 (7-2, left 0) aaa 5 12-mar...