I started a reply when you first posted, describing how various combinations of date configurations and display formats worked together, but I underestimated how much effort it would take to document, so I set it aside and haven’t had a chance to return to it.
One reason it was going to take so long to assemble is that I have yet to figure out Airtable’s handling of dates and times: To me, it’s one of those perfectly logical things that makes no sense. However, in working on this reply, I think I’ve discovered why I had so much trouble figuring things out: I was confusing field configuration with field formatting, which had me chasing my tail trying to understand why dates and times seemingly never wanted to sit still. As time permits, I’m going to try to put together a more-detailed discussion of date and time handling — but in the meantime…
In the example you gave in reply 12, you’ve left out an important part of the date and time formatting routine: Setting the appropriate timezone. Your formula should read
DATETIME_FORMAT(
SET_TIMEZONE(
{DATE ISSUED},
[Your Timezone]
)
‘ddd, MMMM D, YYYY, h:mm A’
)
Where '[Your Timezone]' appears in the formula, you should insert the appropriate timezone identifier.
Now, will this give you what you want? It depends – mainly on how {Date Issued} is configured. If it is set not to use GMT, then the formula should return the value of {Date} in the format specified. If it is set to use GMT, though, the date and time indicated will be shifted by however many timezones you are currently offset from GMT.
As far as the earlier version of your formula returning a time field of 12:00 a.m., that suggests one of several possible situations.
-
The date and time were set using the TODAY() function rather than NOW(); the former returns the current day with a timezone of midnight, while the latter returns the date and time stamp at the moment the formula was evaluated.
-
The date and time field is configured not to include a time field. Such fields, when queried as to their time stamp, return midnight.
-
The date and time field may have originally been configured to support a time field, but at some point the field was reconfigured without a time field. Should it later be re-reconfigured once again to support a time field, the field will have lost all time data and will return, once again, midnight.
It is this last situation I describe as having confused field configuration with field formatting. At first glance it feels like a bug: Configure a date field with a time field, populate it by bringing up the date picker and clicking the 'Today' button, change the format to drop the time field, change the format again to restore the time field — and, suddenly, the time field reports '12:00 a.m.' Somehow, changing the field formatting alters the data… .
Ah, but we didn’t change the field formatting: We reconfigured the field. Rather than altering the display format, we made a fundamental change to the underlying data structure. The proper analogy isn’t along the lines of adding another degree of precision, it’s more akin to changing a number to a single-line text. As such, the loss of time data should come as no surprise.
So, to recap:
-
The only way to be absolutely certain how a datetime value will be presented is to use SET_TIMEZONE(). (Note that SET_TIMEZONE() can only be used as the first parameter to DATETIME_FORMAT() — or, rather, that is the only place it can be used predictably.)
-
Date values are assumed to be in local time unless the ‘use GMT’ toggle is selected. Needless to say, your date values should either all use GMT or all use local time; mixing the two will only end in tears.
-
Date values without an explicit time field will return midnight (12:00 a.m.) on the specified date. Keep in mind, though, depending on where you are in relation to GMT, toggling GMT on can have the effect of shifting the date value back a day.
For instance, consider two fields: The first is a date field; the second is a formula field with the formula cinfigured as {Date} — that is, it takes on the value of the date field. Initially, both are configured without a time field and to use local (not GMT) time. A value is entered into the date field, and the formula field mirrors it, both displaying the same date. Now, reconfigure the date field to select ‘use GMT.’ If the local timezone lies to the west of Greenwich, this will have the effect of shifting the value of the formula field earlier by as many hours as the local timezone if offset from GMT. Since the unshifted time field was set to midnight, or '00:00' on a 24-hour clock, any movement towards an earlier time pulls the value to the previous day. The formula field is now off by a day from the value of the date field.
I think the answer to your question is in there, somewhere. Me? I’m about to shift myself to bed; once there I intend to shift myself forward 4 or 5 hours, at least, without thought of Airtable. :winking_face:
Edit: The square brackets ([ / ]) in the example formula are just to indicate where a location-specific timezone specifier needs to be added; the brackets themselves should not be included in the final formula.
