How to Coda with Over Time Part I
Compensating office hours, extra hours, weekends, holidays
Not all hours are equally paid for all people. Often blue-collar worker get paid more during weekends and even more on on holidays. In case a holiday equals a weekend day, the pay ratio for holidays is taken.
In Part I of this exercise we show how to use a canvas button to set up this calculation. The starting point is a table that that has a kind of spreadsheet look to make it easier for the team managers to fill out the hours worked (and much more, but that is for an other time).
We only ask to fill out the hours. The calculation is based on two criteria.
- Some employees (mainly office workers) get a fixed fee per day, for them there is no paid over time
- Those who get paid overtime get the hours on top of the standard hours (8 hours) paid out with a supplement. There is a higher supplement for all hours worked in weekends and an even higher one during holidays.
We can summarize the conditions in a table for those we do not get paid a fixed fee. As you observe the weekends and holidays have no hours included.
The calculation Set Up
Before I start with setting up a button, I create a canvas formula on purpose. Proceeding in this manner does not permit for actions like AddOrModifyRows(), but shows ín less clicks the outcome → testing my assumptions goes faster. I’ll advice the same to you when setting up calculations in buttons.
My first job is to define the basics: the employees and the working hours.
In the first version of this blog I assumed I needed a SwitchIf(), because it permits to deal with hierarchy in a specific way.
It appeared I was mistaken.
We need an If statement to set the condition apart that deals with the fixed salary in the main calculation (more about this later) and an If statement to define a value that will make the calculation way easier.
The main step → a table with easy to reference values
We start with the latter to make our approach easier to comprehend and understand. Below the table the team managers will be using (they will have a different view) and part of this table is the column ‘ThePercentage”. This one tells which percentage will be applied in case of over time. This column is created via a button; the value is pure text and as such the impact on the performance of the table is limited. On top it will be a hidden column for the team managers, so it won’t hurt their experience when filling out the table.
En the code to generate the percentages is not too long:
The trick is to see that we have two types of percentages, one ís date based (Holidays) and one based on date property — IsoWeekDay(). So we first look for the date based versions because they have priority over the weekdays and weekends. Next we take the values per IsoWeekDay(). The table I reference in the screenshot you see below. If you wonder why the Days have names and numbers, it is to show that I follow the IsoWeekDay() logic and thus this column links to a table about Days that also contains the names of the weekdays in different languages.
Once we have the percentages in the main table, we can reference them and so we do.
Step 02 — weekday logic
The most complicated part of this calculation is straighten out the weekday logic. Holidays & weekends are rather easy as you see further on. All hours worked during these days are paid as over time. So not for weekdays. The contractual hours are country (even sector?) dependent and next you have mainly two ways to deal with over time generated on weekdays. In our case a work day counts 8 hours, but in Belgium a weekday is set as 7.6 hours.
In one approach days with less hours will be compensated by over time before being paid out. In the second approach, we always pay overtime regardless of days with less hours.
We start with the calculation that ignores days with less than 8 hours and per day counts over time.
Step 02 A — only over time
Below the code part that first counts the days with over time and does so to by checking if the WorkedHours are greater than 8 (the chained part after).
Second, we evaluate each day to get the over time per day and we link it to the percentage we already created in the main table. Next we add the 8 hours again.
10 hours worked on a day that requires 8 results in 2 hours overtime, but they are — via 150% — turned into 3 hours, thus this day has 11 hours to pay out.
Third we take the days with less than 8 hours, although no overtime we need to pay for these hours as well. This is the part NoOverTime
Step 02 B— over time as compensation
This approach first compensates for days with less hours than 8 before paying out overtime. We look per employee for the average hours per day worked.
this results in a number of hours per day per employee, in our example for two employees we have the following details. On average 8.2 and 8.5 days worked, the on top is respectivly 0.18 and .54 and we count 11 and 13 days.
Together these elements permit for a final calculation. The numbers 11 and 13 are the result of the count, see below. If you replace Count() by Average() you have the 0.18 and 0.54.
We finish by stating that if the on top is great than 0, we multiply this on top by the worked days and multiply that by the over time rate. This results in the hours to be paid. We add the normal working days (8 per day) and we are set.
This was the most complex part. In our next blog we explore the special days in step 03.
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.
