Photo by JESHOOTS.COM on Unsplash

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.

Generate a date table

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.

the date pickers as controls

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.

Christiaan on ‘Date pickers to define date ranges”

--

--

--

Christiaan uses Coda mainly for planning & calculations of all sorts. Follow me to learn how to Coda with numbers. I blog at least once per week.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

What’s The Difference Between Autocorrelation & Partial Autocorrelation For Time Series Analysis?

What Is Correlation?

From zero to one: Building a Data Science company from scratch

Statistical Ambassador: Year Two

project 4 — data visualization

Identifying Suspicious Trading Among U. S. Senators

Explainable AI-Dashboard

ESG Investing, Data & Graph

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Christiaan Huizer

Christiaan Huizer

Christiaan uses Coda mainly for planning & calculations of all sorts. Follow me to learn how to Coda with numbers. I blog at least once per week.

More from Medium

Coda Packs and Using Column Names

How to Coda your Shopify Pack

How to Coda Sick Leave Part I

How To Coda Breaks in a Time Table