I have the data in one column:
2018-11-08 10:21:59 PST
2019-02-26 07:26:44 PST
2019-02-04 08:08:18 PST
2019-03-11 07:47:03 PDT
This is now a text field, how can I convert this to a date field ? I will will use a new column but what is the Formula to use?
What is the formula to use here
You’ll use the DATETIME_PARSE function for this. However, the PST vs PDT difference can’t be tracked, as the date format specifiers used by this function don’t provide that kind of control.
Assuming your existing text-based dates are in a {Date} column, I originally wrote this formula:
DATETIME_PARSE(Date, "YYYY-MM-DD hh:mm:ss")
However, sometimes Airtable is smart enough to figure things out on its own:
DATETIME_PARSE(Date)
In either case, everything works fine:
Nops I was unable to do it with this column alone
I did have to add 2 new columns:
open-date (Fixer): : DATETIME_FORMAT({open-date},“MM/DD/YYYY HH:mm:ss”)
Date Added:: DATETIME_PARSE({open-date (Fixer)})
Airtable catch that I want date after I did 2 columns
WHy?
Not sure. DATETIME_PARSE by itself worked fine for me on the samples I copied from your original post. It could be that there’s something in the actual data you’re using that’s muddying the waters, and it can’t parse correctly.
What’s the order of your two fields? Does the format come first, and then the parse (as you have them written), or the other way around? Technically formatting is only possible on an actual datetime object, so formatting text before it’s been turned into a datetime object should (in theory) not work. It should be parse first, then format.
One thing that can sometimes throw Airtable off its game is styling of certain characters. It’s known that styled quotes won’t work in formulas, so maybe some of the characters in your text version of the dates can’t be parsed accurately. Did you copy your original example directly from the data itself, or retype it?