PTO — working days
Why and how I replaced my initial set up
You can see my initial setup for handling daily working hours below.
This setup worked well for calculating daily hours worked, especially when everyone worked full-time. In those cases, a half-day off (for sick leave or holidays) was a simple calculation: 0.5 * daily working hours.
However, as the organization grew, we encountered problems due to the increasing variety in work schedules. We had some employees with long working days (see the first line) and others with shorter variations (like the last line). In these scenarios, a half-day off was no longer accurately represented by 0.5 * daily working hours.
Quick fixes might involve complex and hard-to-maintain SwitchIf() functions, but I'm against implementing those when a review of our current setup could lead to a more flexible and maintainable solution.
A Different Approach
Instead of the typical spreadsheet layout, we’ve shifted this logic to a table structure where rows, not columns, are the defining element. You can see how this works below. Admittedly, this view isn’t particularly user-friendly. In Coda, tables serve a dual purpose:
- Data Storage and Retrieval: The default table view excels at this.
- Data Presentation: This includes timelines, cards, charts, etc.
Our setup focuses on the first function: storing data. The appearance is less important than the task it enables: accurately calculating hours per day (part) in cases of sick leave or holidays.
We’ve streamlined the setup to use a single column for time, and filters handle all the necessary outcomes. To ensure unique values in the display column, I’ve included two indexes: a row index and an order index. For each variation, we only utilize the first one (the x.1 variation) and we use a little trick to get only the first one in our relation.
Countries
Work schedules vary by country, with some countries sharing schedules. We’ve tagged the first row of each schedule group with its relevant countries. This lets us filter schedules in the next step based on the employee’s contracted country, keeping the options list manageable.
A form to capture data
Since our PTO tables are primarily designed for data storage, most users aren’t thrilled about manually adding the necessary rows — often, multiple rows are needed just to handle a single scenario like a work schedule with breaks or a full work week. We’ve created a form to simplify the input process. An automation handles the heavy lifting, resulting in the output you see below.
Preparation
Remember, our main goal here is to accurately calculate the hours taken when someone takes a half day off. Let’s consider a scenario: An employee works Friday from 8:30 AM to 12:30 PM, then resumes from 1:00 PM to 3:30 PM. The time off taken impacts the calculation differently depending on whether it’s in the morning or afternoon.
I’ve learned that it’s crucial to examine those edge cases to truly understand the problem. So, let’s start with a schedule that includes two breaks. The person begins work at 8:00 AM, takes an hour break at 12:30 PM, and another hour break at 5:30 PM. Here’s the schedule we’ll be working with:
We’ll use the term “shifts” since it’s commonly used in settings like factories where people have early, late, or night shifts. In a long shift of 14 hours, the periods are often indicated as follows:
- First Shift/Morning Shift: This would typically refer to the period from the start of the shift until the first break.
- Second Shift/Mid-Shift: This would cover the period between the first and second breaks.
- Third Shift/Afternoon/Evening Shift: This would be the final period from the end of the second break until the end of the shift.
Practical
Most workdays are around 8 hours or less. 12-hour days with two breaks are uncommon, typically seen in only a few industries. In a 3-shift scenario, you might be able to take the first or last shift off, but rarely the mid-shift. While it’s technically possible to code the mid-shift as the period between the first and second breaks, it’s not a realistic scenario. So, we’ll focus on the first and last parts of these longer workdays.
The coding part
Rather than multiplying the workday hours by 0.5 for half-day shifts, we’ll now check the shifts directly. Because each day is already connected to a working schedule (or has none) in our booking system, we don’t need that part of the filter. However, we’ll use a canvas formula for demonstration purposes, and this requires us to explicitly mention the working schedule.
Getting the shifts
First we’ll pick the right variation. Then, we’ll figure out the time difference between the start and end times. Check out this example below — notice that we’re using the Sort() function in the formula to keep things organized, which is super important for getting the right answer.
At first, I thought I had figured out how to handle long days with lots of breaks, but looking back, I was mistaken. I only realized my error when I tried to apply it to a night shift.
The key characteristic of a night shift is that the end time is numerically earlier than the start time. For instance, 7 AM is earlier than 10 PM, or in coding terms, its numerical representation is smaller.
For night shifts, simply sorting the times won’t cut it. We need to make some adjustments to the lists before doing any calculations.
The biggest change happens at the start when we collect all the rows that have to do with variation nine. We end up with a list of values arranged from smallest to largest. This works great for regular daytime shifts, letting us subtract this variation from the next one in line (current value + 1), just like you see below (variation 8).
Just adding another variation to handle the next day’s logic doesn’t fix things because of how the values are arranged in the list. During the day, you start earlier and finish later, giving you a nice, sorted list. But when you’re dealing with two days, this logic falls apart.
To deal with night shifts, we have to rethink how the list is organized. That’s why I had to create a new list that includes timeslots from both today and tomorrow, starting from the beginning of the workday. Because these timeslots were already sorted in the first step, I was ready to move on.
[DB Work schedule].Sort(True(),sortBy: [DB Work schedule].theTime).Filter(variations.Contains([var 09])).WithName(base,
base.Filter(type.Contains(working) and defaults.Contains(start)).theTime.WithName(theStart,
base.Filter(theTime >= theStart).theTime.WithName(thisDay,
base.Filter(theTime < theStart).theTime.WithName(nextDay,
ListCombine(thisDay,nextDay).WithName(timeslots,
Sequence(1,timeslots.Count()).ForEach(
SwitchIf(
timeslots.Nth(CurrentValue + 1) > timeslots.Nth(CurrentValue),
timeslots.Nth(CurrentValue + 1) - timeslots.Nth(CurrentValue),
timeslots.Nth(CurrentValue + 1) + 1 - timeslots.Nth(CurrentValue)
)
)
)
)
)
)
)The “+ 1” you see there is the same as adding 24 hours. In Coda’s formula language, it’s like saying “+ Hours(24)”.
Application of the developed logic
Because we’re not dealing with shifts that start in the middle of the day, things are simpler for regular workdays. We can just grab the first and last values and add them up to get our answer.
Applied to standard terms in our forms for sick leave an holidays.
This approach works well for most of the schedules I deal with. However, I want my tools to be comprehensive. That means we also need to account for breaks. In certain jobs (like bus drivers) breaks can be quite long, sometimes even longer than the final leg of a trip (if poorly planned).
The trick is to remove the breaks so we can add up the remaining values. We do this by using the same logic as earlier, but focusing only on the values related to breaks, and then we throw in one small but crucial extra step.
First, we call the result “allSlots”. Then, we filter out the breaks to get a list of break times. Since breaks have both a start and an end time, you’ll notice the first two items are connected, and so are the third and fourth items in this little list with two breaks (resulting in a total of four items).
To prevent getting the difference between item 3 and 4, we use “by: 2” in the sequence, which tells it to skip every other number. If you’re not familiar with this, give it a try!
Using the same logic as before, which also handles breaks that go past midnight, we call the result “theBreaks”.
The final step is to filter “allSlots”, keeping all the values except those in “theBreaks”.
And that’s it! We’ve solved it. This was a tricky one to crack.
I hope this article was informative and helpful. Did it help you to solve a problem you unlike would have solved other ways? What about a donation?
My name is Christiaan, and I regularly blog about Coda. While this article is free, my professional services (including consultations) are not, but I’m always happy to chat and explore potential solutions. You can find my free contributions in the Coda Community and on X. The Coda Community is a fantastic resource for free insights, especially when you share a sample doc.
