How to Coda with Over Time Part II
Compensating office hours, extra hours, weekends, holidays
In our previous blog we showed two ways of dealing with over time during a weekday. 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.
Complexity requires reflection. My general understanding is that the more requirements we face, the cleaner the code.
Step 03 — Special Days
In case we have no weekdays, we have weekends and holidays and both have in common that all working hours are paid as over time. We call these days, special days as you see below.
Not weekdays I wrote as the previous function with a Not() at the end. Often I go for this approach. A weekend is IsoWeekDay(). Contains (6,7) and a weekday becomes IsoWeekDay(). Contains (6,7)´.Not() Ít helps me to see the opposites in a code snippet and it is often shorter.
We only have to multiply the hours with the percentages we already printed in the main table and we reference via chaining and that is it.
The last but one part is taking the sum of the variatons we created. Notice that the Sum() part arrives at the end and not half way. We keep the lists we work with open as long as we can.
Before we can multiply with the hourly rate per employee we have to close some named functions, while leaving open the fundamentals we started with. This part looks easier than it is. You might be tempted to add the product function direct after summing up to learn the hard way that it does not work. The Coda code editor is not a great help in this regard. It easily gets messy and sluggish. More about how to avoid issues in our next blog.
These blogs may give the impression that this kind of calculations is easy peasy, certainly if you do it every day. However that is not true. I started off on the wrong foot first by applying a technique that gave me for all dates per employee an outcome per compensation type assuming that SwitchIf() would solve the list issues at the end. That was not working for (I can tell now) obvious reasons. Instead in these kind of calculations you have to group data sets and evaluate the items in each sub set to bring them together at the end as one data set per employee. It sounds easy, right? It took me some time to see it, partly because I was busy with the the business rules.
One particular insight was important to get the job done and that was the printing of the percentage in the main table. I got that insight during my daily meditation last Friday (June 10).
Meditation, breath work, push ups, a cold shower and black coffee, the morning routine that works for me.
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.
´-
