Photo by Roman Skrypnyk on Unsplash

Coda paid time off (PTO) — 7.0

Managing PTO cancellations and double bookings

Sometimes employees accidentally double-book or try to cancel days they haven’t booked. Instead of complex date restrictions in the form, I built a system to handle these situations gracefully:

• Double bookings: We’ll book only available days and let the employee know about any overlaps (we don’t book).
• Cancellations: We’ll cancel existing bookings and inform the employee if they try to cancel unbooked days (which we don’t process).

How data enters Coda

Employees fill out a form and an automation triggers a “transpose” button, creating a new row in the request table for each date. One crucial column is “request type” — either booking or cancellation. We first verify if each date already exists for that employee and check its current status (booking or cancellation). We use descriptive words for status, often clearer than checkboxes that might need further explanation.

We have the following scenarios.

• first request for this date
• cancel for requested date
• cancel for non requested date
• booking after a cancel on this date
• cancel on cancel on this date
• booking on booking on this date

In case you forgot: we’ve established a solid foundation for PTO tracking so far. Our current system effectively calculates PTO values (1 for full days, 0.5 for half days) by considering the date range, workday status, request type (booking or cancellation), and filtering by the specific user. This allows us to accurately adjust the PTO balance.

How can we incorporate handling for erroneous bookings to prevent them from impacting the PTO count, ensuring the system remains robust and reliable?

I jotted down some scenarios on paper:

1. Book vacation for Aug 27
2. Cancel vacation for Aug 27
3. Book again a vacation for Aug 27
4. Cancel again vacation for Aug 27

I initially thought a simple even/odd logic would work, but real-life PTO requests are trickier. This example shows why:

1. Normal booking
2. Accidental double booking (instead of a cancellation)
3. Cancellation (correcting the previous mistake)

Patterns like this are surprisingly common, so we need a system that can handle them gracefully.

example of mistake and correction of mistake

This is how we achieve the desired result: The first SwitchIf() produces a 1 or a 0, and the second one outputs messages such as 'double booking' or 'cancellation of a non-booking'.

The value in the first SwitchIf() comes from the table below. Standard bookings and cancellations are handled correctly, but we avoid altering the balance in cases of double bookings or cancellations of non-existent bookings.

This elegant solution came to me after a frustrating day wrestling with a concept that almost worked. A good night’s sleep did the trick! This new approach focuses on two key elements: the last and preceding values. While it may seem a bit complex, remember, my clients pay for implementation, not the time I spend building my expertise!

We’re not quite done yet! These output terms will be used in notifications to both employees and HR. I’ll dive deeper into that process in a separate blog post.

But before we wrap up, there’s one more scenario to address: what happens when someone gets sick during their holiday? Their holiday should be canceled, holiday balance adjusted, and sick leave updated. We’ll tackle that complexity in a future post. Stay tuned.

I hope this article shed some light on the topic and helped you solve a tricky problem! If you found it valuable, consider a donation to support my work.

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.