If you would like to incorporate a date (and time) type field into a formula field, the best way to format that date is using the DATETIME_FORMAT function. For more information on DATETIME_FORMAT, and other formulas, we recommend checking out the formula field support article.
The DATETIME_FORMAT function will allow you to reformat the data from a date-type field into a string following your specifications. The formula will follow the recipe of:
DATETIME_FORMAT([yourDateTimeField], '[format specifier]')
The format specifier will allow you to dictate the structure of how your date should be returned. The format specifier can be something like 'DD-MM-YYYY,' 'YYYY/MM/DD,' 'MM.DD,' etc. For a full list of supported format specifiers, please see this article.
See below for a quick example of how DATETIME_FORMAT can transform a date field from one format to another.
{Location} & " ON " & DATETIME_FORMAT({Date}, "DD-MM-YYYY")
Notice how we transform the date's format (MM/DD/YYYY) from the Date field to the Formula Examples field (DD-MM-YYYY) above.
Example 1 (Dates)
For the above Experiments example table we have three fields setup:
- Experiment Name: the primary field, which is a formula, for this table that concatenates the word "Experiment #" and the Autonumber field (Test ID):
"Experiment #" & {Test ID}
- Test ID: An autonumber field for tracking our unique experiments.
- Test Date: A date field that tracks the date on which an experiment took place.
We'd now like to update the Experiment Name field to include the date each experiment occurred. If you add a date field to a formula without using the DATETIME_FORMAT function, the formula will display both the date and time in a simplified ISO formatting. If your table is going to used and read by people, you'll probably want to alter your date to be a little more human friendly.
Here is what our Test Date field looks like without any formatting:
"Experiment #" & {Test ID} & {Test Date}
Not super fun to look at, or easy to read. To make things clearer—wrap the date field you are working with using the DATETIME_FORMAT function. Now take a look at the Experiment Name field:
"Experiment #" & {Test ID} & "—" & DATETIME_FORMAT({Test Date}, 'MM/DD/YYYY')
Easy to read and work with!
Example 2 (Dates and Times)
You can use DATETIME_FORMAT to configure both the output for dates and times. We now have a time for each of the experiments in the Test Date field:
We'll want to incorporate those times to the Experiment Name field. So we can tweak the DATETIME_FORMAT to include the time of each experiment.
"Experiment #" & {Test ID} & "–" & DATETIME_FORMAT(Date, 'M/DD/YYYY h:mm')
Occasionally, you may encounter a problem in which the timestamps in the DATETIME_FORMAT are offset to GMT. Notice the times below don't align:
You can fix this problem in one of two ways. The easier way is by checking the "Use the same time zone (GMT) for all collaborators" in the date field's configuration menu.
You can also fix this manually by using the SET_TIMEZONE function inside of your DATETIME_FORMAT function. A complete list of Airtable's supported timezone identifiers can be found here.
"Experiment #" & {Test ID} & "—" & DATETIME_FORMAT(SET_TIMEZONE({Test Date}, 'America/Los_Angeles'), 'M/DD/YYYY h:mm')
Which will lead us to the same, consistent, results: