Coda the 1st day of the previous month
lower the floor using a simple function
On twitter I noticed the question you see below.
It is labeled as intermediate level, so a bit more difficult than beginners I guess. Why is that? Have a look.
As you see, you don’t need a pack for this, this is not even a bit difficult.
One suggestion to create a new function to solve this specific puzzle troubles me.
I wish there was a StartOfMonth formula, would make this sort of thing much easier
Actually we may have already too many functions in the Coda Formula Language Toolbox. Before I share my thoughts on this, first options that work and don’t work.
When you want to add a day to a given date, you can add 1 to the date like below. The date of today is April 28, 2023. When you add 37 it adds 37 days.
When you want to add a month, you cannot say date + month(). When you do, it adds 12 days.
Adding a year in the same manner does not work either as you see below:
This may be confusing when you start working with Coda.
Dates are lists of items
In the Coda Formula Language a date is composed of three items:
- Year
- Month
- Day
These are items in a list. You see this logic when people want to reorder the lists because they believe that 03/04/2023 can easily be confused with 04/03/2023. One is the USA notation, the other covers the rest of the world.
Each item in this list has a specific property as we see in the function Date().
Year()Month()Day()
We turn this date into a list — see below — and from here on you can play with the Nth() value to change the position of the items in the list.
Moving month
There is a function that permits you to move months backwards & forwards. It is named: RelativeDate() . That name confuses me.
You see that it permits me to move one month back. That may seem useful, but it also can be confusing when you want to learn how to Coda because there is no option to replace months with days or years. Would that have been the case, this function would have outputted what the name suggests.
There is a function that does allow to play with the date time units and it is called DateTimeTruncate() and used by the Coda CEO to get the job done.
I would not have thought of it and when I see it, I will not use it. It is too complex for the result we are after.
To come back to my main point, to lower the floor in Coda, I suggest to remove the RelativeDate() function.
You look at a date as a list of items with specific properties and you add (negative) numbers. Below how it goes with years.
Before we end
What if we want to avoid that the sender has to work in the weekends as defined in the USA and we follow the Coda logic that Sunday is the first day of the week and Saturday the last day of the week. Sunday is thus weekday() 1 and Saturday is number 7. Well by now you may already see the function you need:
This is indeed a bit more difficult. You create a list of dates and via a filter you take out the weekends and of this list, you take the first value you convert to the date format to make it easier for humans to read.
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. More about me below.
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.
