I did a time clock for my office.It can be done a few ways. If you supply a file I will take a look and get you some ideas.
It can depend on how breaks are tracked if at all. Are the actual workers clocking in or is this entered by a foreman/site manager?
In the simplest sense you would base everything off of the initial clock in time for the shift. That makes getting the rest pretty easy depending on if your minimum pay increment is 1 hour .5 hour or .25 hour. It looks like Scale will have a reference to the actual pay per hour. Many unions pay a four hour minimum and you need to factor that in depending on what hour they started as well. Do you pay 1.5xOT+10% for a shift over certain hours that runs into the premium hours?
Once you have all the small pieces it is just some algebra getting it all together.
Thank you so much for your response and thank you so much for your generosity. I'm new to FileMaker Pro and have had troubles trying to do this with Excel in the past. So far (with my 1 day of experimenting) this is becoming a MUCH better solution for me. I'll answer your questions and will post the file online so you can see my attempts.
The minimum pay increment is every 6 minutes (.1 hours). I created a drop-down menu with pre-set times rather than rounding. The pre-set also assures that the calculation will work correctly.
Scale references a hidden "Rate" field. This changes depending on which "Scale" is selected. However, rates increase every year so I should find a better way to put the rate in.
You're absolutely correct about the minimums! My union has a 4-hour minimum as well as a 9-hour minimum.
The 10% and 20% night premiums do, indeed, apply additionally only to the 1.5xOT
I appreciate every bit of help and advice you may give. I'm not expecting you (or anybody) to do a complete job for me (and for free) but I will be thorough in what I'm trying to achieve, and learn.
(Clicking the above link WILL automatically download the file)
Here are the rules I want to include in my form:
- Y-1 = $x.xx
- Y-9 = $y.yy
- 0-4 hours worked = 6 hours pay
- 4-12 hours worked = 6 hours pay + 1.5x hours worked between hour 4-12
- 12-15 hours worked = 6 hours pay + 1.5x hours worked between hour 4-12 + 2.0x hours worked over hour 12+
- 0-9 hours worked = 9 hours pay
- 9-12 hours worked = 9 hours pay + 1.5x hours worked between hour 9-12
- 12-15 hours worked = 9 hours pay + 1.5x hours worked between hour 9-12 + 2.0x hours worked over hour 12+
Golden Time (2.0x):
- 2.0x starts after 12 elapsed hours from "Time In #1" even if there's time off during lunch
- 1.5x hours worked over hour 40 in the week
6th day (1.5x):
- 4-hour : 0-12 hours worked = 1.5x 6 hours pay
- 4-hour : 12+ hours worked = 1.5x 6 hours pay + 3.0x hours worked over hour 12+
- 9-hour : 0-12 hours worked = 1.5x 9 hours pay
- 9-hour : 12+ hours worked = 1.5x 9 hours pay + 3.0x hours worked over hour 12+
7th day or holiday (2.0x):
- 4-hour : 0-12 hours worked = 2.0x 6 hours pay
- 4-hour : 12+ hours worked = 2.0x 6 hours pay + 4.0x hours worked over hour 12+
- 9-hour : 0-12 hours worked = 2.0x 9 hours pay
- 9-hour : 12+ hours worked = 2.0x 9 hours pay + 4.0x hours worked over hour 12+
- First ½ hour meal delay or fraction thereof = $8.50
- Second ½ hour meal delay or fraction thereof = $11.00
- Third and each succeeding ½ hour meal delay or fraction thereof = $13.50
- 8:00 pm - 1:00 am = Current rate + 10% (in addition to only to the 1.5xOT)
- 1:00 am - 6:00 am = Current rate + 20% (in addition to only to the 1.5xOT)
- Starting 0-4 hours after latest Time Out = Paid during these hours, continue at whatever scale was paid last (even if at Golden Time)
- Starting 4-8 hours after latest Time Out = Continue at whatever scale was paid last (even if at Golden Time)
Coming up with some ideas. This is pretty fun! Here's a look at some nonsense that I'm coming up with so far.
When computing elapsed time between time in and time out where the time in may precede Midnight and the time out may come after midnight. Use timestamp fields instead of time fields.
TimeStampOut - TimeStampIn will compute the elapsed time in seconds without any need for special handling to handle intervals that span midnight like you would with two time fields.
And the timestamp values can be created with the timestamp function that combines separate date and time values.