Photo by Zetong Li on Unsplash

How to lower the Coda floor?

Use patterns you can repeat and adapt

Christiaan Huizer
8 min readMay 10

--

The last few weeks I came across Coda challenges on Twitter dealing with getting the right date each year knowing that in a given month it is always the x Sunday or y Thursday.

The most surprising solution was generated using AI. A table was created on command and filled out with the correct date per year. Impressive.

Such a table is a good demonstration of what eventually will be possible, but is not yet practical. Companies and private persons need a table containing relevant holidays with dates living on rows. An AI per row in a holiday table would be a better solution, which is possible by the way. Since the current AI makes mistakes, we still want to have a formulaic solution.

In this blog I’ll show how the application of a central characteristic of the Coda formula language helps you — even as a newbie — to rapidly understand the logic and to create your own solutions.

The pattern

In Coda when we discuss functions we deal with lists of items. All sorts of items like names, ingredients, colors, dates and so on. These items have positions like item 3, 5, 13, 28 etc. By sorting items, the position of an item can change.

Dates are numbers with properties. A month for example, can be perceived as a list of dates starting at the first of the month and ending on the last day.

When we need father day or Thanksgiving, dates that shift and are year specific, we only need one date in a specific month. Thus one of the many dates.

Let’s take Thanksgiving, it is always the 4th Thursday in November.

Imagine we have list of dates (how we get this list is for later) then we look for all the dates with the property Thursday, which results in a sub list of dates and of that list we take the 4th item, thus the item living on the 4th position.

Creating a list

In Coda there is no function that gives you per month the list of dates. There is a function called EndOfMonth() showing how many days there are in each month. However, with the number of days available, you don’t have a list of dates. First we look into some properties of dates (which are — as we mentioned before — numbers). Each date comes with properties like:

  • weekday name
  • the position of the day in the week (which is different in Europe compared to the USA)
  • the week number (like wise the above)
  • the day (the position in a week)
  • the month (the position in a year)
  • the year
  • etc. The full list you find here

Composing dates

When we want to have a list of dates between the first and the last date of a month, we need these two dates and to get them we use the function Date() as you see below. It is a little gem.

We have the option to create each item in our list manually, that would go like below. We use the function List() and inside this list, items are added.

3 manual ways to create a list

In the first version I type the numeric values. The second one is already a bit more dynamic, I create the year with a function — Today().Year(). In the third version I added like wise the month.

All of this is not practical.

We need a function that does this job for us and luckily we have Sequence() in our toolbox. First we create two dates. We need these to generate a list of numbers representing the dates in November related to the year of today.

creating a list of dates

There is in my opinion no need to use endOfMonth(), only when you don’t know that November has 30 days.

You see the list of 30 dates of which we only need one that fits two criteria:

  • always a Thursday
  • always the 4th Thursday of November

Reducing the list — step 01

We start looking for all the Thursdays in this month and we do that by using the property WeekdayName(). This one output the weekday names in English and we check for each item in the list on the property ‘Thursday”.

Checking for all Thursdays

Working with the weekday name avoids that a flaw in the Coda formula language creates problems. This flaw is that the function Weekday() can output two different numbers, depending on your doc settings. In the USA the week starts on Sunday, while in Europe it starts on Monday. The same function can thus — based on your doc settings — output a weekday you don’t want.

When you apply the USA settings, you can also write:

The weekday syntax makes it easier in a later phase — when you have a bit more experience with this pattern — to look for multiple days in a week, like day 3 and 5 or you want to exclude the USA weekends writing: Contains(1,7).Not() . Tip, don’t write currentvalue.Weekday() = 5 . It blocks creativity while Contains() opens opportunities. Second be careful. Weekday logic requires you to remain attentive until Coda solves this quirk.

Reducing the list — step 02

Once we have reduced the list to a sub set of the main list (we count 5 Thursdays in this month) we want to know the date of the 4th item in this list. We ask for the 4th position. Positions in lists we get via Nth().

getting the 4th item

This number hides the specific date property we show using ToDate().

turning the item into a date

The pattern applied

When we don’t have a list to work with, we can generate one using Sequence() and Date() and out of this list we can get specific values by filtering on date properties. The last step is getting the first, second, third or other position. Below you see how I responded to an AI written function.

Other patterns

I noticed quite some variations in the answers and all of them seemed to have produced a correct outcome. However none of them was as easy to read as the one I proposed.

Below the one of the CEO of Coda:

These contributions follow a different pattern.

The solution that comes closest to the pattern I prefer is from the great Coda connaisseur Joostmineur :

The responses of Joost, Shishir and Peter have in common that the 4the Thursday is not explicitly isolated. Instead it is the result of choice made in the initial set up. These makers preselect the date range using their reasoning capabilities (or asked AI to take it over as Peter did).

They work, but these contributions are harder to explain to someone new to Coda than the creation of a list filtered on the name or weekday to finally ask for the required position in that sub list.

Why lowering the floor matters

Lowering the floor in Coda not only intents to produce the correct outcome, but also to produce an outcome you can repeat elsewhere with little tweaks. This is the first reason why lowering the Coda floor matters: you easily recognize comparable patterns. Second, this approach is grafted on a core characteristic in Coda that everything is a list. Based on a positive outcome here, you may see a list logic at work when — for example — you have to create a function to get the next birthday. As shown in a previous blog, this is a list logic as well.

We can take it one step further. You need to send notifications only on working days that vary over time based on working schedules. Again you see list logic in action.

So far the examples related to dates. A comparable logic can be applied in the context of the so called previousRow logic. This is a misleading term since in tables the only order is a sorted order, even when implicit. Nevertheless also in this context we evaluate lists.

Third by lowering the floor, we get a higher ceiling. The sky is the limit once you see patterns like these. You can create many smart interactions in your docs. You know that when you don’t see the lists you need, other maybe will. You hire people like me to get most out of Coda and so we all win.

To conclude, look for the easiest pattern possible . They have as characteristic that they can be applied in many scenarios. Once found, you can refine and elaborate.

My name is Christiaan and I support SMB with calculations (budgets and — Human Resource — planning) and I prefer using Coda to get the job done.

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.

Coda comes with a set of building blocks ー like pages for infinite depth, tables that talk to each other, and buttons that take action inside or outside your doc ーso anyone can make a doc as powerful as an app (source).

Not to forget: the Coda Community provides great insights for free once you add a sample doc.

Interested in a two way sync solution?

Check out this blog: it is easy and goes fast ⤵️

--

--

Christiaan Huizer

I use Coda mainly for (HR) planning & (budget) calculations. Follow me to learn how to Coda with numbers. I blog at least once per week.