How to Coda with EU date formats?
My week starts on Monday
In the Coda community much has been written about the need for more date format types. 03/04/2021 is in Europe something else than in the United States. Starting the week on a Monday gives different weekday numbers and EU week numbers differ from the one in California. When you make a date based planning, this becomes easily an issue.
Solving the issue seems not a priority the moment for Coda, though a bit strange seen their popularity in Europe, this is it for now. With this blog I provide a work around for anyone who struggles like I did.
Canvas versus Table
In tables you can format dates and times as you wish once you put the column to date or date time. By default is the USA format,so first month, next day, last year. (MM/DDY/YYYY). Though often inconvenient not a real problem.
On the canvas it is different story. Even if the date is formatted like DD/MM/YYYY in the table, this date referenced on the canvas as outcome of a function (like first available date for ….) returns the USA format in most cases (but not always…). That is annoying, see below a few examples. The direct reference to the first date in the list presents okay.
After adding two days it falls back to the USA format and this not what you want. This inconsistency creates trouble of a kind you cannot afford.
You won’t have this, in case you wrap the date in an unsupported formula to output the format of your liking, see example below:
So far the visual and inconvenience related to dates on the canvas. Our next subject relates to the weekday number logic in a table.
Their week starts on Sunday
Here in Europe we apply the rule that the week starts on Monday, while in the USA they start already on Sunday. Below the difference and how you can create a EU logic with a simple trick.
When the shit hits the van…
All dates can be read as numbers that contain properties you can extract via
Weekday(), WeekDayName(), Day(),Month(), Year() and so on. In other words, once you have a date, you have the number of that day in the week, the name of the weekday, the month, the year and much more.
As said, any given date results in a weekday, which is number that tells you day 1 is Sunday, day 2 is Monday and so on. As long as you work with dates and only dates, the problem is rather contained.
We run into trouble when we ask users to select a weekday name and we relate this to something like we drive the boys to sport every odd week on Saturday and every even week on Sunday. Or we set up a planning for parents whereby the mother takes care of the boys in even weeks at Monday, Tuesday and Thursday, while in odd weeks it is Monday, Tuesday, Friday, Saturday & Sunday.
Find the differences
Below you see how the differences play out. In the Coda logic, the last week of the year is already number 1, the Sunday is a 1 and not a 7
Below the formula that extracts properties of the date written as currentvalue. In terms of code is rather straight forward so far. The only complication is getting the EU day number using
The week and day numbers are quite different and that is confusing.
Solving it in 4 steps
To reduce the complexity of the puzzle we first create an additional table that contains all the weekday names linked to the Coda weekday number logic and with a column that contains the EU weekday number logic. Since we talk about 7 rows, there is no need for any formula. It is a table purely based on text and that makes it fast. It looks like the one below.
Step two is bringing in the data we need. In our case I needed the weekdays in Dutch. You generate a formula that takes all the values of the column of your choice and use this in combination with Select Items, like below.
This is a great solution, also when you want to combine many lists to allow users to select items from. The downside is that you cannot use the Chaining logic. You know the dot that permits you to jump from to table to table to reference the list of your choice. This option is only available via a LookUp. The thing is that a LookUp returns the values in the display column and this might not be the values you are looking for. Certainly not when you create tables for users with a different language preference, the French weekdays will not please the Dutch speaking community and vice versa.
In our case we want to offer Dutch speaking users the NL version and French speaking users the FR version, while still using one table in the back. This implies we have to translate weekday names like woensdag or mercredi into the Coda weekday number 4, while for the user this is day number 3 (starting at Monday).
In short the easy solution is to create one doc per language and set the display column on the language of your choice. Chaining will make it rather easy. Here we move on with the complicated challenge.
Step 3 is to link the weekdays in the language of your choice to the weekday numbers Coda works with by default.
Below how we find the related EU day numbers using a filter instead of chaining. It feels like a detour and if it is, it is a necessary one.
The lookup defaults to the value in the display column, the Coda value is stored in a different column to return a text value as you see below:
The last step is to make use of this logic in a filter that enables us to align days with — in our example — the place one stays on for example Monday.
Dates can be read as numbers, these numbers contain properties and one of them is the Coda weekday number. We look for the dates in the defined date range and check if the property
Weekday() contains one of the weekdays we assigned to a place to stay. In that case, the place could be used at a give date.
Since we need a value ‘staying’ that lives in the same table as the days we set up a filter like below:
The Dates is a
currentvalue I got via the
Sequence(thisRow.[DB Period].From,thisRow.[DB Period].To).FormulaMap(CurrentValue.WithName(Dates,
In the project I am working on for the moment I am not yet done, but the filter I created is a good start. It permits to add extra criteria like an odd or even week number and the name of the person involved.
On the canvas you need
FormatDateTime() to show the date value as you see fit. In tables you pull in the days of your liking via a select list and you link these to the Coda Weekday number. It is a bit of work and I have to admit, it easily gets messy, but it works and that is what counts most.
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