How to get monthly date range and only months on date column?


  1. I want to have a column that shows date ranges. For example, from 1-1-2021 to 10-2-2021.
    Can this be done?

  2. I used a Custom representation to show only month name and year and works. The problem is that still I need to select a date and that destroys the charts.
    What I would like to do is to be able to select BY MONTH only and not by DAY.
    Like this: image

Here is an example document: Grist

  1. You can write a formula combining two dates, e.g. "{} to {}".format($Start, $End) or "{:%Y-%m} to {:%Y-%m}".format($Start, $End) to format only the year and month.
  2. Formatting the date in a formula, e.g. "{:%Y-%m}".format($Start), means that the column will actually contain only the formatted string. This is different from the date formatting setting in the right panel which doesn’t affect the underlying data. The chart behaves nicely when given data that doesn’t contain a day, and even automatically parses the string back into a datelike object.

Instead of starting my own thread, since I have a similar issue which either isn’t addressed by this solution, and/or my understanding of aforementioned solution is going above my head…

I’m looking at having a single column that contains a range of X year - Y year using a dropdown. I tried your solution, removing some of the content to make it " “{:%Y} to {:%Y}” and it seemed to error out. I originally edited my Year column to add the formula to the upper portion, with no dice, then added a test column at the end of the table as a fresh, unadulterated column without any previous formatting.

Copied my table here and made it public… If this is a new request/issue, please LMK and I’ll make a fresh topic.

Hello Jon! Welcome to the forum!

The formula Alex provides is using the Python function .format(). The formula fills the {} brackets between the quotations marks with strings specified in the parentheses in .format(), in respective order.

For example, say you had a column of first names ($FirstName) and a column of nicknames ($NickName). You want to format the names together in a third column as Bill, aka Billie. The formula would be written as

"{}, aka {}".format($FirstName, $Nickname)

I added this example here.

So to make the formula work, you need to specify the Start and End columns in your formula between the parentheses in .format(). Those start and end columns need to be date columns, because the :%Y between the {} is taking a date and formatting it to only extract the year. In the same example I have the formula working correctly. It’s written as "{:%Y} to {:%Y}".format($Start, $End)

It sounds like what you’d like to do is select a car’s make and model, and have the year and range automatically fill in. Is that right? Let me know if I’m on the right track and I can help you with that.

Seems I missed this comment, perhaps I am not getting email notifications…

Not … exactly. What I was hoping to do would be to use the dropdown to display a date range from a year to a year, (say, 2016-2020) without showing days or months, and to select only a single year if I prefer (even if this has to be done as “2016-2016”.

Added bonus if I can also select a year to current, which would display as “2016+” or “2016 - Current”.

I’m not looking to have it pull content from other columns… In my particular use case I am assembling a list of potential cars to purchase, and since certain desirable features such as a type of engine or type of transmission is only available on some years, I want to be able to quickly consult my Grist DB to check if the age for a listing of a specific model fits my criteria.

Perhaps this isn’t possible? I tried to simply use the date range option and used the custom rule to display it as “YYYY” only and this still asked for, and displayed, both month and day information

I see! Unfortunately, we don’t have a date range column type – though it’s an interesting feature request!

Would it be possible to write the year range as text in a single column? You’ll still be able to sort that column.