Photo by Centre for Ageing Better on Unsplash

How to Coda your quarters?

Use the RelativeDate() function and a start date

While I was watching this video I noticed in the comments:

Peut être remplacer le mot quartier par trimestre ….

It put a smile on my face. It shows the importance of language, making sure we understand each other.

Oui. Après cela veut dire la même chose : quarter = trimestre
L’outil est en anglais, il ne faut pas avoir peur de l’anglais ;)

As important as the language is, so is clarity in the formulas we develop. In this blog I suggest an alternative to what is shown in the video. Our starting points are:

• dates have properties like month() and monthName()
• quarters are periods of 3 consecutive months and per company the starting date of a quarter can vary. Traditionally we use Q1 as Jan, Feb, March and so forth, but this is not a given rule for all companies. We thus need a logic that takes care of any specific start date.

We start with point two: the start date

Quarter logic

Even in a formula language we come across assumptions. The Weekday() function assumes the USA date logic and to get a weekday logic as used in Europe we need the alternative IsoWeekday() . The same for WeekNumber() which the alternative isoWeekNumber() as us used in most parts of the world, but not in the USA.

It is important to make implicit assumptions explicit before we start calculating. The assumption that a quarter starts at Jan 1 and ends on Dec 31 is questionable. We make this one explicit to have more options.

Below a table that shows how data architecture is crucial and avoids complexity. It is a bit of extra work, but worthwhile the win I guess.

When looking at the video, the maker created a list of month numbers on the canvas and referenced that list in the formulas. A like wise approach is translated into the two tables below.

To test my assumptions we create a third table with random dates between 2020 and 2025. That is on purpose before and after the date range we mentioned in the example which only deals with the year 2024.

I asked AI the following:

I need a table with 30 rows and two columns:
Column one is Name and this one should be empty
Column two is TheDate, this should be a random date between 2020 and 2025.

This table permits to check our simple solution, have a look:

we filter on the name of the month

First I did not see why my solution without ‘Name’ in the formula did not work, but that became clear when I looked at the value type. You can replace var01 with the 02 and 03 variations if you need, that is the main advantage of this approach, it is flexible.

May you need the start date and the end date of each respective quarter type, you can continue with the developed function.

I hope you take away from that filtering is the way forward in these scenarios. The maker in the video understood that as well when writing the canvas formulas. By the way, I liked the video, it showed good Coda skills.

Limit tables

Of course you can skip the tables and fully integrate the logic you assume to be valid for a long time in your code. The code snippet to get the last quarter date is the following.

As you see, no extra tables are required. This is how I mostly solve this issue. And when you want to have the starting date, you can reuse this function and subtract 3 months:

  Quotient(thisRow.TheDate.Month(),3).RoundUp().WithName(TheQ,
    EndofMonth(
      Format("{1}/01/{2}",
        Product(TheQ,3),
        thisRow.TheDate.Year()
        ).ToDate(),0)).RelativeDate(-3) + 1

Related solutions based on the extra tables

I added also the logic of the related months and their dates in line with the logic I proposed. Below the screenshots, the tables you find here.

first month name

the related date using the next day logic

the last month name per quarter

the last date using end of month

All rather coherent and good to follow, also when you are new to Coda.

Quarter logic

My name is Christiaan and blog about Coda. Since the summer of 2023 often (but not only) about how to Coda with AI to support organisations dealing with texts and templates. The latest major Coda AI update was on Dec 7, 2023.

Updates to Coda AI credits, analytics, and features

Why I focus on Coda AI you can read here: ⤵️

Why I focus on Coda AI?

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. 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.