Converting records from text to date

Does anyone know how set up a script to edit text fields (imported from xcel) - specifically I want a date + time text field to be separated into a date only so that I can convert it to a date field type for future automation processes. Example of what I want on the left and what I am working with on the right. I’m a complete newbie to scripting and pretty new to airtable generally so any advice is greatly appreciated!

Update: I managed to work this out using another script for splitting fields — Split text to columns - #6 by Mike_Pennisi

This could be done with the help of a formula field. The first step is to extract the date, which can be done with a regular expression (I can’t see the full name of your field, so I’ll just use “Original Date” for these examples):

IF({Original Date}, REGEX_EXTRACT({Original Date}, "[^\\s]*"))

That will extract everything up to—but not including—the first space, which falls after the date. With that string, you can then wrap a DATETIME_PARSE() function around the extraction:

IF({Original Date}, DATETIME_PARSE(REGEX_EXTRACT({Original Date}, "[^\\s]*")))

Airtable can natively parse several common date formats—including the YYYY-MM-DD version that the formula extracts—without specifying the format directly.

In fact, you might try directly parsing the entire string and see if it does the job without needing the extraction first:

IF({Original Date}, DATETIME_PARSE({Original Date}))
1 Like