Photo by Alex Bertha on Unsplash

Coda time off (PTO) — version 2.0

Relate work schedules to time off

In our last blog we demonstrated that via forms we gather the required data: working days & schedules per working day. For each date we have to check how this date fits the working schedules. In the end the results per departments, we share via the recently launched sync pages. In this blog you learn how to get the working schedules per required holiday date and thus you have the hours (via chaining).

Linking dates to schedules

When a new working schedule begins on April 1 and changes on May 15, we assume the following:

1. The first schedule is active from April 1 to May 14.
2. The second schedule takes effect on May 15.

If you take time off between May 10 and May 18, we evaluate each date against the applicable schedule:

• May 10–14: Evaluated using Schedule 1
• May 15–18: Evaluated using Schedule 2

This ensures that the correct schedule is applied to each day of your time off, accounting for the mid-month change in working schedules.

Example data one person and two schedules

We gather the date range via a form that contains the least fields possible. We capture the user and relate it to the employee and the N+1. This relation is based on a tables linking roles, departments and direct reports.

a simple form to capture the requested holidays

The “DB Requests” table is populated with dates obtained from the “DB Holidays” table, as illustrated in the image. A button is provided to transpose this data. The code responsible for the transposition process is shown below.

It’s worth noting that the Coda team seems to have underestimated the complexity and importance of data transposition. This oversight creates difficulties for many users and even prevents some from fully utilizing the platform’s capabilities.

Below the code snippet to tranpose.

Sequence(thisRow.fromData,thisRow.endDate).ForEach(
  [DB Requests].AddRow(
    [DB Requests].requestDate,CurrentValue,
    [DB Requests].weekdayName,CurrentValue.WeekdayName(),
    [DB Requests].people,
      [DB People].Filter(
        theUser.Contains(thisRow.people))
      )
      )

Our focus is on getting the days we have have to compensate. Therefore we have to check per date which schedule to apply, if there is any. We do that in three consecutive steps.

Step 01 — get the schedule

This is a classic problem that we solve by using a filter to compare values before and after, utilizing the ‘less than or equal to’ operator for numerical comparisons.

getting the work schedule per date

In our next steps, we’ll integrate two crucial elements:

1. The identity of the person requesting Paid Time Off (PTO).
2. The names of the weekdays corresponding to the PTO dates.

It’s important to note that we deliberately use weekday names instead of numbers. This approach is taken to avoid confusion arising from the weekDay() function, which can output different numbers due to hard-coded regional settings. Such inconsistency in numerical representations of weekdays can lead to errors and misunderstandings.

This strategy ensures that our PTO tracking system remains consistent and easily interpretable, regardless of the user’s location or system settings.

Step 02 — define the working days

With the active working schedule per date, we can check if a date and weekday is a working day. This requires that we compare two variables, the active schedule and the weekday name.

getting the active weekdays

Step 03 — define the working hours

Once we have the working day and the schedule, we have the hours. That is a matter of chaining.

getting the hours via chaining

This exercise establishes the foundation for our next step: comparing requested Paid Time Off (PTO) against available PTO. We’ll begin by focusing on holidays, as they present a simpler scenario. Typically, holiday allowances are calculated on an annual basis, making them easier to track and balance.

In contrast, sick leave policies are often more complex, usually requiring monthly balance calculations in most countries. This added complexity in sick leave tracking can be challenging to implement effectively. I can attest to this difficulty from personal experience, having tackled similar systems in the past.

I hope you enjoyed this article. If you have questions feel free to reach out. My name is Christiaan and blog about Coda. 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. You find my (for free) contributions in the Coda Community and on Twitter. the Coda Community provides great insights for free once you add a sample doc.

All the AI features we are starting to see appear — lower prices, higher speeds, multimodal capability, voice, large context windows, agentic behavior — are about making AI more present and more naturally connected to human systems and processes. If an AI that seems to reason like a human being can see and interact and plan like a human being, then it can have influence in the human world. This is where AI labs are leading us: to a near future of AI as coworker, friend, and ubiquitous presence. I don’t think anyone, including OpenAI, has a full sense of all of the implications of this shift, and what it will mean for all of us. — source: Ethan Mollick from One Useful Thing.

More about Coda AI and Coda Brain: