Convert Text to Date

[Abraham_Bochner]

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

[jord8on]

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:

[jord8on]

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:

• On the left you’ll see the original email from Airbnb - note the Date, that I want in Airtable
• On the right you’ll see the Zapier Email Parser app. When it gets the receives an email it’s pulling in those red dots, which I’m guessing means there some kind of character or formatting in the CSS or styling of the email. But it’s weird b/c the Zapier Email Parser seems to remove all formatting… but for some reason those red dots show up.

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…

[Justin_Barrett]

jord8on:

Can the Airtable TEXT field retain formatting being input from a Zap? Like if the zap was pushing those red dots through or something?

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.