Save the date! Join us on October 16 for our Product Ops launch event. Register here.
May 06, 2019 04:05 PM
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
Jan 15, 2020 10:10 AM
Yeah the interesting thing to note was in my post a few threads ago… if you look at the third row down… the formula worked!
I like what you’re saying about some hidden data that might be coming through. I’ll keep my fingers crossed that your TRIM suggestion will work.
Thanks again for taking the time to help :raised_hands:
Jan 15, 2020 01:47 PM
Okay @Justin_Barrett! I’ve done another test and here’s another tidbit that might help with my situation…
Unfortunately the “TRIM” function did not allow the “Checkin” formula to accept the data being input from my Zapier email parser.
Take a quick look at the image below:
So in a different TEXT field called “Check-in”, I’m using my zap to send over that same bit in the green box (illustrated in the image above), and the information is coming in just fine, as illustrated here:
Can the Airtable TEXT field retain formatting being input from a Zap? Like if the zap was pushing those red dots through or something? Though it doesn’t seem so. The date is coming through as MMM DD, YYYY
I’m so confused right now. The most confusing thing is that one of the dates does convert properly…
Jan 15, 2020 06:46 PM
That sounds very likely. I’m not sure what those red dots represent, but they’re obviously extra characters that Airtable doesn’t render, even though it most definitely recognizes them at a formula level. But why it successfully parses some dates and not others is a mystery.
One thing to try is to wrap SUBSTITUTE()
around the {Check-in}
field reference in your formula, and substitute the mystery bits out. Copy one of those mystery characters from Zapier, paste it into the SUBSTITUTE()
formula. Here’s roughly what it would look like with that mystery character represented by :x: :
DATETIME_FORMAT(DATETIME_PARSE(SUBSTITUTE({Check-in}, "❌", ""), "MMM DD, YYYY"), "YYYY-MM-DD")
Replace :x: with your mystery character and see how things go. If that doesn’t work, try copying one of those mystery characters into a plain text editor that (I hope) will display it for what it really is, and share a screenshot of that here so we can dig more deeply.