Converting records from text to date

[Caitlin_Warlick]

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

[Justin_Barrett]

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}))

[Jessica_Eastlin]

@Caitlin_Warlick-Shor - I was wondering if you could explain your solution further? I’ve been able to extract my date information using a formula, but can’t figure out how to turn the results of that formula into a date field type as you described in your original post. Any guidance would be appreciated!