Automatically populate next time
Determine the date and time when your SLA is due to be met
This was the question I noticed shortly in the community before it was removed.
Hi all
I’m wondering if it is possible to include/consider working hours in a formula (9am-5:30pm Mon-Fri in particular)!
I have a table to track tasks. I have a column for the date that the task is added and based on certain SLA, I want the due date & time to automatically populate.
For example, if a task has a 4 hour SLA and is created today at 9am, then the due date will be today at 1pm. I can easily set up the formula/buttons to calculate this for each task and SLA. However, I’m struggling when it comes to “stopping the clock” for non working hours. For example, if a task is added today at 3pm, I do not want the due date to show as 7pm today, I want to show as 10:30am the following working day.
I came across a question in the community that resonated with my PTO work, so I tackled it and turned it into a helpful blog post. It’s essential to lay a solid foundation with a well-organized data structure. Then, you can fine-tune your filters, and you’re practically done!
Although there are more tables, these are the two core tables in our set up.
As you can see, this setup is straightforward but effective, even if your workday ends on Friday at 3:30 PM (like many companies in Belgium).
Below, we’ll work with a table where we begin by considering the next working day, in case we need that information. I’ve highlighted Monday in blue for easy identification.
Let’s explore the logic behind this setup. I’ve accounted for the US notation, where Sunday is considered the last day of the week and the first day to start counting the weekday(). The final outcome isn’t significantly affected, but if your document is set to default with Monday as the first day, your weekday() order will change. I’ve previously mentioned the challenges of a function having two potential outcomes based on document settings. In any case, this is the logic we’re using.
The logic dictates that the weekday value of the start date should be less than the filtered result. If this result is empty, we default to the first value in our pre-defined list named “outcome,” which, in this case, is Monday.
We’ll need this logic a bit later when we determine if an SLA can be completed today — the simplest and most common scenario.
Since we have two values per day, we’ll use the last one, which represents the end of the workday (endDay). This value is used as the first argument in our SwitchIf() function. If the start time plus the SLA duration is less than or equal to the end of the workday, we simply add the SLA duration to the start time. This should be easy to follow for most makers, regardless of their Coda expertise.
Next, we’ll figure out how much of the SLA time spills over into the next working day. I’m combining steps 2 and 3 because in step 3, I included the ToHours() function, and the screenshot illustrates why that’s necessary.
Finally, we just need to add that remaining time to the next workday, which we already identified in the first step and stored in the “nextDay” column. First we add the time as you see below.
We’re almost there! All that’s left is to incorporate the date logic, and for that, we’ll once again use the “nextDay” logic, as shown below.
We achieve the SLA deadline by combining the outputs of both named formulas. This solution adapts seamlessly to changes in SLA types per client and modifications to daily start and end times.
Of course, real-world scenarios are often more complex. Factors like employee sickness, holidays, and public holidays can impact availability. When working with clients, we typically define individual work schedules to accurately reflect employee availability. While there’s much more to consider, this approach provides a solid starting point. Below the Coda doc.
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.
