Regular Time in Coda for Starters
A step by step guide to calculate regular time in your team
While I was reviewing parts of my code related to over time I noticed that it easily gets complicated, even when you do this on a regular basis. This blog intents to show in a few simple steps how you can create your own set-up to understand the regular hours worked in a production environment. In the example I work with 4 employees.
Three types of Over Time
In our example hours on top of a work day of 8 hours are paid at at 150%, weekends 175% and holidays 200%. We need to know if we talk about a regular day, a weekend or a holiday (can be in a weekend or on a regular day). To keep the calculation simple we already have in our main table a supportive logic. it is column that contains the percentages we apply.
All Regular Hours
We stick to one question: give per employee an overview of all regular hours. These hours exclude over time during regular days, weekends and holidays. The complexity is two fold:
- During week days the first 8 hours are regular
- During week days some people work less than 8 hours
We first filter out the weekends and the holidays by filtering on days that have 150% in the column ThePercentage.
The ThisRow logic via a button
Below you see how we mimick the thisRow logic we are used to in table. Outside the table we apply a technique I described here.
The outcome of the mimicked thisRow we rename Step01 and with these rows we start calculating, the details of the above overview follow.
We start with the function that gathers all the normal hours in a month. These include days that have 8 hours or more, but we only need the 8 hours. Since all numbers are integers, we write greater than 7 → (8,9, 10 etc.). Below in three steps how that goes.
The second part is about the days with less than 8 hours and for these days we take the sum. Two employees fit the criteria. It is a variation on the above, but one step shorter.
The last task in this blog is to multiply the outcome by the hourly rate after we combined the mentioned calculations with a plus operator.
The CurrentHourRate is a smart function the DB People that takes the actual compensation per employee as you see below.
The output of this calculation shows us the compensation for normal hours, not for the over time. But once you see this happening, you also can see how to calculate the over time, right? All elements are present, it is about reshuffling the parts and adding the .Not() behind the ThePercentage.Contains(1.5) to get the job done.
I hope that these detailed steps makes it easier for you to set up your own calculation. It is mainly a matter of trying and excising.
I hope you enjoyed this article. If you have questions feel free to reach out. Though this article is for free, my work (including advice) won’t be, but there is always room for a chat to see what can be done. Besides you find my (for free) contributions to the Coda Community and on Twitter
My name is Christiaan and I support SMB with calculations (budgets and planning) and I prefer using Coda to get the job done.
Not to forget: the Coda Community provides great insights for free once you add a sample doc.
