Breaking down timecard hours

[madman411]

Hi all

I've inherited a project that I'm struggling to implement into a solution I've been asked to work on to see if FileMaker is an appropriate solution.

Currently I'm working on a basic timecard system that uses a series of overtime schedules to break down hours worked into straight time, time and one half, double time, etc. Currently this is not calculating pay, but rather separating the hours worked during the day into their respective threshold categories.

To begin with I've created a database and table called "hours testing" and have the following fields: schedule name - day number (1-7) - date - call - meal 1 out - meal 1 in - meal 2 out - meal 2 in - wrap - hours - 1x - 1.5x - 2x - just to create some test records and test some calculations.

A second table (joined via "schedule name") has the 'overtime schedules' which was an excel file given to me as the base framework of how the company wants to calculate time thresholds. It's packed with quite a bit of data which I'm told was a timely process to assemble, so I would ideally like to leave it as-is. In the 'overtime schedules' table I have created fields based off of the column headers in the excel file and imported the excel file.

Within the "hours testing" table I want to be able to create records, with time entered as tenths, and have the 1x, 1.5x and 2x fields calculate based on what respective schedule is chosen. The caveat here is that some values within the excel file use "e" and "w" (elapsed and worked) to determine when the threshold changes. According to my superior, "e" elapsed means "elapsed hours, pay out golden time before deducting meal period" and "w" worked means "worked ours, pay out golden time after deducting meal period." It is my understanding that "golden time" refers to a provision where after 16 hours employees are paid at the full day rate per hour beyond hour 16. 

I have successfully created the sample file and can add work hours and it calculate the hours worked, minus breaks, but I need assistance with creating the calculations for the 1x, 1.5x and 2x breakdowns. Further breakdowns like 3x and 3.75x are going to be a later addition. These schedules account for union contracts and different labor laws. I need this to work before presenting the example.

As basic hour entry is, well, basic, I'm attaching just the excel spreadsheet that was given to me in hopes someone more versed could assist with the calculation breakdowns.

In reference to the excel file, "Sch 1 to 5" refers to the first 5 working days ('day number'). "Sch 6D" refers to 6th day worked (different overtime provisions) and the same for "7D" (7th day), also with different overtime provisions. 

Any assistance or guidance is appreciated.

Untitled.xlsx

Edited February 12 by madman411

[comment]

This is difficult to understand. A worked out example using data from a selected row would be useful.

In general, I would be worried that the imported rules might change and affect history records. You need to lookup the relevant data into the timecard record and perform the calculations from there. 

 

 

Edited February 12 by comment

[madman411]

@comment thanks for replying!

 

I have included an example below. Shown in list view you'll see a few example records. Under 1x, 1.5x and 2x you'll see two rows of fields. The first row uses a "threshold" which are designated in the header. I'd like to remove this manual process and utilize the imported overtime schedule to break the hours down. The second row of fields is my attempt at testing the calculations.

And you're correct - if the rules change ideally older time entry records would remain unaffected. 

hours_testing demo.fmp12

[comment]

Sorry, I don't follow the logic of your file. When I said "a worked out example" I meant for you to show us how you would do the calculations manually. As it is now, I have no idea what the numbers/symbols in the overtime schedule table mean and how they should be used to calculate the results you want.

I think you should split problem into two separate steps:

1. Get the applicable data from the overtime schedule table and store it in a usable format;
2. Calculate the results.

It seems to me that the 2nd step is the simple part here. For example, if you know that a person worked 15.5 hours and that overtime starts after 8 hours and double time after 12 hours then:

Let ( [
hours = 15.5 ;
overFrom = 8 ;
doubleFrom = 12 ;

double = Max ( hours - doubleFrom ; 0 ) ;
overtime = Max ( hours - double - overFrom ; 0 ) ;
regular = Min ( hours ; overFrom )
] ;
List ( regular ; overtime ; double )
)

will return the correct split of:

8
4
3.5

 

WRT to the 1st step, it seems to me you could simplify things by splitting each line in the overtime schedule into 4 records (one for each day type).

 

Edited February 12 by comment

[madman411]

25 minutes ago, comment said:

Sorry, I don't follow the logic of your file. When I said "a worked out example" I meant for you to show us how you would do the calculations manually. As it is now, I have no idea what the numbers/symbols in the overtime schedule table mean and how they should be used to calculate the results you want.

I think you should split problem into two separate steps:

1. Get the applicable data from the overtime schedule table and store it in a usable format;
2. Calculate the results.

It seems to me that the 2nd step is the simple part here. For example, if you know that a person worked 15.5 hours and that overtime starts after 8 hours and double time after 12 hours then:

Let ( [
hours = 15.5 ;
overFrom = 8 ;
doubleFrom = 12 ;

double = Max ( hours - doubleFrom ; 0 ) ;
overtime = Max ( hours - double - overFrom ; 0 ) ;
regular = Min ( hours ; overFrom )
] ;
List ( regular ; overtime ; double )
)

will return the correct split of:

8
4
3.5

 

WRT to the 1st step, it seems to me you could simplify things by splitting each line in the overtime schedule into 4 records (one for each day type).

 

Thanks Comment.

The overtime schedule isn't something I have control over. It's maintained by someone else who tracks the rules and contract terms and is provided to me as/when changes to the overtime schedule are made (i.e. labor law is changed or a contract is changed). I can put a request in to see if the person who maintains this is able to adjust the formatting. I believe it was originally exported from the system they were previously using. I was hoping to use it as-is.

FWIW, "E" refers to elapsed. "W" refers to worked. This is likely used in determining monetary payout. Per my supervisor:

"E" Elapsed - means "elapsed hours, pay out golden time before deducting meal period" and "W" Worked - means "worked hours, pay out golden time after deducting meal period." It is my understanding that "golden time" refers to a provision where after 16 hours employees are paid at the full day rate per hour beyond hour 16.

"Sch 1 to 5 xx" would be day 1 to 5, "6Dxx" would be 6th day worked, "7Dxx" would be 7th day worked. 

This is my first attempt to integrate this into a FM solution to "automate" it. Your assumption is correct in that you would use Wrap - Call + M1Out - M1In + M2Out - M2In to determine hours worked minus breaks. Most (but not all) schedules break that down to 8 hours (straight) 4 hours (time and one half) and double after 12, but as you can ascertain from the spreadsheet it does indeed vary based on the contract and/or if the work week exceeds 5 days. Your second step indeed calculates correctly, but the overFrom, doubleFrom, tripleFrom, etc would need to extract the correct value from the appropriate schedule

I appreciate your help however. I need to dig deeper into how the previous guy was using this data for calculations.

[comment]

4 minutes ago, madman411 said:

The overtime schedule isn't something I have control over.

But you do have control over how you import it. Or you could import it as is, then split it into separate records in another table.

 

[madman411]

6 minutes ago, comment said:

But you do have control over how you import it. Or you could import it as is, then split it into separate records in another table.

 

This is true, now you mention it. If you have a [generous] moment to demonstrate how you would use another table to split the records I'd be very grateful. The way the existing setup is created, filemaker is seeing it as multiple fields inside of one record. 194 records x 26 fields.

[comment]

The simplest way is to perform 4 imports, each time importing the schedule name along with the group of fields that belong to a specific day type. Immediately after each import use the Replace Field Contents step to populate the day type field in the just imported set.

You can do this when importing the Excel file or as an internal import from one table to another.