Formatting a date field as text

Airtable Questions
1

I was recently contacted by someone who wants to update a text field with a date using an automation. When the automation copies of the date to the text field, the text shows up as an ugly string (2023-03-14T11:10:37.000Z) instead of a “friendly” text string (March 14, 2023).

The reason for this is because the formatting options in the user interface don’t apply to how the field values is used in automations or formula fields. Instead you get the ISO text string that Airtable uses to stores dates under the hood.

To get a friendly date in your automation you need to create a formula field that formats the date to your liking. Then include the formula field in your automation.

DATETIME_FORMAT({date}, "MMMM D, YYYY")

This formula is documented in the Formula Field Reference. Note, that depending on how your timezone is set, you may also need to use the SET_TIMEZONE() function, which is also documented in the Formula Field Reference.

2 Likes
2

Since Airtable (and every database system, for that matter) stores dates in UTC format, I’m pretty sure you MUST use some sort of timezone adjustment unless you live in the UTC timezone (Greenwich, England).

3

It is also possible that the “use same time zone” setting is selected and set to GMT. Until recently that was much more common than I would have liked.

I’m also guessing that the question was about a date/time field. If it were a date only field (very unlikely), the date would already be GMT midnight.

So it’s best to check time-zone settings to be sure.

1 Like