Beginner Calculation Assistance

[John S]

Greetings,

First let me thank you in advance for assistance with my question.

I have been using FMP for a while, but not doing any calculations.

I am working on a DB to calculate the cost of a laborer. The laborer gets paid at regular time, time and a half and double time. I would like the DB to be able to determine how many hours are regular time, time and a half and double time based on the time the person starts and ends working.

I have been able to get the "total hours" field to calculate the hours worked based on a time out field - time in field calculation. It displays the hours worked in HH:MM:SS time. I would prefer it to not display the :SS, but that is I am sure a simple adjustment.

I have tried to put this formula into the Regular Hour field to calculate hours.

If (Out - In) < 8 (Total Hours = Out - In) If ( Out - In) > 8 Total Hours = 8)

When I try and submit this formula it says there are too few parameters. I have tried adjusting the formula in a number of ways with no success.

What I would like to accomplish is this:

If (out - in) < 8 hours then regular hours = the actual hours

If (out-in) > 8 hours then regular hours = 8 and time and a half hours = (total hours - 8 hours) and it will display them in their appropriate fields.

I hope that makes sense. I know that there have been a lot of feature advancements between 8.5 advanced and 10 advanced. Am I asking this version to do something that it is not capable of or is it just user error.

If I need to provide additional information let me know. I am working with a temp DB to establish the formulas and calculations before I move forward with the complex design.

Again, thank you in advance for your assistance and wisdom in helping me figure this out.

John

[LaRetta]

Hi John, welcome to FM Forums!

Well, I do not wish to sound pessimistic, but tracking payroll isn't quite this simple. I usually suggest hiring a payroll service (whose job is to track the laws, state & local taxes, withholding and such). It is the SAFEST and EASIEST method for both employer and employee. But to just provide you with how many hours are regular, how many are time and a half, and how many are double time, we need to know a few more things:

1) Do you have an Employee table attached?

2) Do you have a rates table which indicates the rules, ie, holidays or weekends are usually double time but that would depend upon business rules, whether employee is salary (and exempt) and so forth.

3) Are you using timestamps for your employee to clock in and out?

4) Do you pay to the quarter hour and do you round up?

5) What are you field names which are involved and what are you table names involved and how are they related?

6) Overtime rules also need to consider whether it wraps to a different day. If the employee normally works evening shift, they do not get overtime if overlapping to another day. These rules need to be clearly defined because nothing is more important than a person's paycheck.

Tracking also should be handled specifically, to allow for 'forgiveness' issues which would over-ride a calculation (authorized by the manager/owner). So if you wish to only determine whether regular, time and a half or double time, we still need to know quite a bit more to provide the correct calculation or solution.

UPDATE: You truly also need a EmployeeRate table which allows for (and tracks) an employees rate-of-pay over time and the salary should be pulled from that table, depending upon the day worked. What you do NOT want to do is hard-code rates of pay into a calculation.

DOUBLE-UPDATE: Truly, it is best to talk to a payroll service. Then all you do is provide them with the employees total hours and THEY decide, according to the rules you set up with them per employee, what rules apply. It is SOOOOO simple! I've written payroll programs before and it is not for faint of heart and I would never do it again; particularly nowadays with services available.

LaRetta :wink2:

Edited January 24, 2009 by Guest
Added double-update

[RalphL]

You have 2 calculations: RegularTime and OverTime. You can use either the If or the Case statement to do these calculations. Since you started with the If, I will use it.

The calculation for RegularTime is much like you started but you left out the ( & ) whicch show the start andd end if the If and the ;'s which separate the 3 parts of the If statement. Use:

If (TimeOut - TimeIn > 8 ; 8 ; TimeOut - TimeIn)

OverTime is similar:

If (TimeOut - TimeIn > 8 ; TimeOut - TimeIn - 8 ; 0)

[John S]

Greetings,

Thank you for the welcome.

This is not payroll tracking in the traditional sense. Thank you for you cautions, but this is doing basic calculations, not paying taxes.

I have attached the database so that any questions you haven't asked could be answered by looking the DB.

1. There is not an employee table. Typically, we do not track stage hand names as we go from city to city.

2. There will be a rate table established so that the rates for a particular stagehand department will autofill for the form. This will be created further down the road.

3. We don't clock people in and out in the traditional sense. This may be a feature that is added later, but not at this time.

4. Time is based on the hour or occasionally by the half hour.

5. See the attached DB

6. The only overtime rule that might come into play is on a longer event that extends weeks at a time. This again will be an advanced feature added at a later time,

I hope this helps you better assist. Thank you again for your assistance.

John

Labor_Test.zip

Edited January 24, 2009 by Guest

[LaRetta]

Hi John, there is no attachment. To attach your file, zip it and, at the bottom of your post response you will find a link called Manage Files.

But truly, if all you want is a calculation, not even tracking it per employee or whether weekend and at double-time and so forth, then what Ralph presented will do the trick. If you need anything further with your solution (and since you are not properly tracking payroll tax or 1099), I won't be able to assist you with it, but I'm sure others aren't so bound and will jump in and help. :wink2:

[John S]

Greetings,

I have added the file. Not sure why it didn't add the first time, but it is there now.

[John S]

Greetings Ralph,

I have input your formula to create the calculation. As you can see from the attached DB it did some interesting things. Can you point out what I am missing?

Thank you in advance for your assistance.

Labor_Test.zip

[comment]

Time is calculated in seconds. The expression:

If ( TimeOut - TimeIn > 8 ; ... )

means "if the elapsed time exceeds 8 seconds, ... ".

I would suggest:

Hour Reg = Min ( Total Hours ; 28800 )

Hour Time Half = Max ( Total Hours - 28800 ; 0 )

Result is Time for both.

Remember that if your rate is hourly, you must use time * rate / 3600 to calculate the amount.

[RalphL]

My Dumb. Time is in seconds. So the 8 should be 8 * 3600 or the Out - In should be divided by 3600.

These calculations are numbers not time.

See the attached.

Labor_Test.fp7.zip

[John S]

Greetings,

I am so great full. I did not know that it was calculating by second versus hours/minutes/seconds.

Thank you for pointing out the error in my understanding. I appreciate you being willing to share your wisdom.

John