Date pickers to define date ranges
How to coda with dates ranges
I was about setting up a tool for clients that included an availability check on a certain date. In Coda you can get a calendar rather easily. You type / then ‘calendar’ and off you go with a calendar on your canvas.
An alternative is a calendar view of a table. This implies that you have table with dates. The set up of such a table is rather easy with a button and goes fast, see below.
The elegant trick (Merci Max) is that if the table has a count (is true) it deletes the actual version, if not it generates a fresh one based on the combination of Sequence(), Concatenate(), ToDate(), FormulaMap() and AddRow() . This methodology is handy during testing with variables.
The result you see below
Next you can turn this table into a a calendar view, but not directly. I first got this and that surprised me. The Column Name is configured as Date, I respected the USA notation, but still it did not work.
The solution is silly but works, you have to allow Coda to add and extra (empty) column because it does not recognize the date values… On top you get a calendar view that is way larger than the table you defined, so it goes before the dates you set. I feels like something goes wrong and this is not the path I want to take.
The date pickers
Then we have option three: the usage of date pickers and there are three types:
- Only dates
- A date range
- Date & time combined
You get them via the logic below. You type / and next ‘Date’ this prompts the three controls you see as well as some tables with a name that contains ‘date’ as string and it even proposes (a silly) pack. For relative dates you do not need a pack…
Anyway the controls are in my opinion the way to go once you have a table that contains dates. Below a few examples to give you an impression.
As you can see they output date information we can use to filter table views.
Using a Control to filter a table
This is where the magic happens. You have a table with for example rooms and you want to check which rooms are still available.
I have three rooms to offer and some days all rooms are sold, some days none and other days one or two.
The result is a large list of dates that related to none, one, two or three rooms. We use the date picker to see if a room is available.
First the logic below is not going to work. The filter on the view of the table takes the first and the last date of the range and not the days in between
The solution for this problem is to add a function that gives us the in between dates. It is a variation on the button we wrote above.
We can add additional criteria to filter out even more options. This is for a next blog as is the methodology to set up a sample table with rooms.
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
My name is Christiaan and I support SMB with calculations (budgets and planning) and I am mainly using Coda to get the job done.
Not to forget: the Coda Community provides great insights for free once you add a sample doc. Paid consulting is often not the way to proceed.
