Grist expresses dates and times in two ways. The first is the
Date column type, which represents a calendar date, with no time of day, and
not associated with any particular timezone. The second is the
column type, which represents a calendar date with a time of day which
can be linked with a timezone.
DateTime column types support different formatting
options. When a column
is set to be a
Date or a
DateTime, a date-picker widget will let
you select the date on a calendar when editing a cell.
When working with dates in formulas, the dates are Python datetime objects. That allows you to do some powerful things, but can be unexpected if you’re not familiar with them.
Making a date/time column#
For a general introduction to setting the type of columns, see Columns and data types. To tell Grist that you intend to enter only date/times in a column, over on the header for the column, find the drop-down, and select “Column Options”.
Then in the side panel that opens on the right, pick “Date” from the “Column Type” drop-down. Or, if you want dates with times, pick “DateTime”.
Then you can choose your preferred date/time format. For the “DateTime” type, you can also choose the timezone. When you convert a column from another type, such as “Text”, you’ll see a preview of the conversion results, and will need to click “Apply” to complete conversion. You can come back and change settings at any time.
Now when you edit a cell in this column, you will have help for selecting dates and times.
Inserting the current date#
You can insert the current date in a cell using
⌘ + ; (semicolon) (Mac) or
Ctrl + ; (Windows).
You can insert the current date and time using
⌘ + Shift + ; (Mac) or
Ctrl + Shift + ; (Windows).
When editing a date cell, the date entry widget has a “today” button for today’s date.
Parsing dates from strings#
The DATEVALUE function converts a string that represents a date into a
object. It’s simple to use and it will auto-detect different date formats:
You can also use Python’s
datetime library, which provides two helpful functions:
strptime() and strftime().
For example, let’s say you have a table of movie sequels and their release dates (as strings). You’d like to parse out the actual date to be able to sort the table properly. Here’s how you would do that:
- First line imports the datetime library
- The second line splits the string into two parts and returns the second part (Python arrays are zero-based).
- The third line uses Python’s strptime
function to parse
the date (e.g. “May 19, 1999”) into a datetime object. The first parameter to the function is
the string to parse, the second parameter is the date format that the string is in. Take a look
at the format
options to see if
the example format string
%B %d, %Ymakes sense. (Note: You could’ve also used
DATEVALUE(d)to achieve the same result.)
The result has a true date column and can now be properly sorted chronologically, with “A New Hope” at the top. For historical reasons, the first Star Wars movie is considered to be Episode 4.
And, because the column type is selected as a date, you can use the “Date Format” in “Column Options” to select the format in which to display the date.
For some situations, you may wish to use the dateutil python library. For example, if you live in an area where dates typically start with the day and then the month, you could use this formula:
import dateutil dateutil.parser.parse($date_text, dayfirst=True)
Once you have a proper date column, often you’ll want to do date arithmetic such as calculating the difference between two dates. The simplest way to do this is to use the DATEDIF function which takes two dates and the unit of information to return (Days, Months, or Years).
You could also use the minus sign to subtract two dates, but you might be surprised at the result:
This happens because subtracting two
datetime objects as we did in the example above, results in a
which represents, “A duration expressing the difference between two date, time, or datetime
instances to microsecond resolution.”
In Grist (and Python) you have to be more specific above how you want to display the date
difference. For example, to get the number of days from the returned timedelta object, use its
If you want weeks or years, just divide by 7 or by 365. (Divide by 7.0 or 365.0 to include a fractional part in the result.) If you want hours, multiply by 24.
You can also use specific functions to get what you want. For example,
DAYS is a common function
in spreadsheet apps that returns the difference between two dates:
Getting a part of the date#
You’ve seen how to parse the date, display it in different formats, and do date arithmetic. But what if you want to get more information about a specific date, such as getting its day of the week?
One option is to use the WEEKDAY function, which behaves as it does in Excel, returning 1-7 for Sunday-Saturday.
Alternatively, we can use the strftime function:
Yet another option would be to reformat the date using Date Format in Column Options (see the date formatting reference).
DateTime columns represent moments in time. The same moment will look different in
different timezones. In Grist, the timezone is set on each
DateTime column. For instance, if the
timezone is set to “America/New_York”, it will show the values in New York timezone to
collaborators anywhere in the world.
A Grist document has a global timezone setting, which serves as the default timezone for when you
create a new column of type
DateTime. This global timezone is set to your local timezone when
you first create a document. You can see or change it by
clicking on your profile picture or icon, and selecting “Document Settings”.
If you insert the current date and time using
⌘ + Shift + ; (Mac) or
Ctrl + Shift + ; (Windows)
DateTime column, it will be inserted as a true timezone-aware timestamp, and shown with
the timezone set for that column.
If you do the same in a
Text column, the date/time will be inserted as the text appropriate for
the document’s global timezone setting. Similarly, inserting the current date into a
will produce the current date according to the document’s timezone.
- Python cheatsheet for strftime, for using with
- Date formatting cheatsheet, for specifying the date/time format in column settings.
- dateutil library, extensions to the