Proportional Overtime Export Explained
Great news, CrewTracks fans! As promised, our proportional overtime export will be available in your CrewTracks app starting riiiiiight… now. As we mentioned in the last blog post, our “User Time” export has always included overtime hours, but it did not include information about which job the overtime applied to. For that reason, we are changing the “Manual (CSV)” job time export to include this information. As part of this process, some columns have changed in the export, so if you rely on a consistent column order, you might need to make one-time adjustments to accommodate the new export format.
In other words:
In most cases, the changes won’t affect you at all. But if you have some excel wizardry or processes set up on your end that we’re not aware of, we want to help you update those as swiftly and smoothly as possible.
Does This Affect Me?
Hey, great question. If you run a “Job Time Export” that includes your accounting software’s name, this update doesn’t affect you.
This update might affect you if you export data from CrewTracks using the “Manual (CSV)” export type. In most cases, we provide raw job time data to an accounting software, and the accounting software sorts out the dates and times to allocate overtime to jobs appropriately. But not all accounting systems can do that, so we have created a proportional distribution formula that calculates and allocates the overtime before exporting the data.
In other words, we’re doing some more heavy lifting so your accounting software doesn’t have to. That said, if you’ve been doing some of this work on your own via excel or a pre-formatted import in your accounting system from the Manual (CSV) Job Time export in CrewTracks, it is likely that this update affects you.
By the way, if you think you’re ready for an accounting system upgrade, we partner with some great folks who would love to help you get into an accounting software that’s specifically designed for a company like yours.
In the meantime, here’s how we’ll calculate overtime.
How We Calculate Overtime
First of all, you’re going to want to set up your company’s work week. Just go to Setup > Organization Settings and choose a day of the week for your “Week Start.”
Great. Now, for a given work week as you’ve just defined it, all time logged up to 40 hours is recorded as regular time, i.e. pay type REG.
After more than 40 hours is worked by an employee during the work week, their time is recorded as overtime, i.e. pay type OT. If the employee works on more than one job or more than one item on the date 40 hours is surpassed, then a proportional distribution calculation is applied for that day. This calculation gives a weight to each item an employee works on for that date. The more time applied to a given item, the larger the weight will be. After the time has been proportionally distributed according to their weight, then each weighted item is multiplied by the total regular time and total overtime for the day. Again, the more time an item has been worked on, the more regular and overtime will be applied to it.
After the day where the regular/overtime threshold is crossed, any time after that is recorded as overtime.
Let’s Go Through a Couple Examples
The company has a work week that runs from Monday through Sunday. Robert works 52 hours and 40 minutes during the week. So the company needs to pay him 40 hours of regular time and 12 hours and 40 minutes of overtime. This scenario applies to both examples below.
Costing Detail Example 1 (the easy one):
Here we see that at the end of Thursday he had worked a total of 35 hours. At the end of Friday he worked 44 hours. So 5 hours of time on Friday needs to be allocated as regular time, and the other 4 hours of time needs to be allocated to overtime. All of the time on Saturday will be overtime hours.
In this example there is only one job with one item on the schedule. So all 5 hours of regular time and all 4 hours of the overtime hours on Friday will be allocated to that job and item. And all of the overtime on Saturday will also be allocated to that job and item. Easy.
This is what the breakout would look like:
Now let’s look at a slightly more complicated example.
Costing Detail Example 2 (same scenario, but more than one job on the threshold date):
In this example we have the same amount of time each day as we did in the previous example. At the end of Thursday, Robert had worked a total of 35 hours. At the end of Friday, he had worked 44 hours. So 5 hours of time on Friday needs to be allocated as regular time, and the other 4 hours of time needs to be allocated to overtime. All of the time on Saturday will be overtime hours.
But now instead of just one job and item, on the day the regular time crosses over into the overtime, we have two jobs and three items. So how will the regular time and the overtime be split among these three items?
In this case, CrewTracks will do a calculation and give a weight to each item based on how much time was spent on each. That weight will then be applied to the amount of regular time and overtime that needs to be distributed on that day. This automatic calculation will then show up on the Job Time export.
This is what that breakdown looks like:
The time on the first job and item is all regular time up through Thursday. Saturday has only one job and item worked on, and all that time is overtime. On Friday, the day when the time crosses over from regular time to overtime, a calculation and allocation is needed to break out the regular time and the overtime for each of the three items worked on that day.
The Bottom Line
At CrewTracks, we innovate by combining vision with feedback. We’ve worked with our customers to make these changes, and the result is more information in the hands of our users as well as an automatic overtime calculation. This will empower our customers to save even more time and money with CrewTracks, and this is a direct reflection of our purpose: We help and innovate for the people who build and maintain our world.
As always, please reach out to us if you have any questions. We’d love to hear from you at firstname.lastname@example.org or 801-742-5555. Thank you for making CrewTracks an integral part of your daily operations.