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:
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?
@Justin_Barrett I have another similar issue. I auto-import data from Twitter into Airtable through IFTTT. The date has to come in as plain text (i.e., Long Text). Its comes in as this: August 04, 2019 at 04:04AM
I want to convert that to: 2019-Aug-04 OR 2019-08-04
What formula can I use in Airtable? DATETIME_PARSE(Date,âYYYY-MM-DDâ) didnât work in this case. The formula itself âtookâ but the âAugust 04, 2019 at 04:04AMâ converted to âErrorâ
Welcome to the community, Callie! With the DATETIME_PARSE() function, the pattern that you give it is the one matching the string youâre parsing, which is âAugust 04, 2019 at 04:04AMâ in your case:
DATETIME_PARSE(Date, "MMMM DD, YYYY at hh:mmA")
What you get back is a datetime object, which then needs to be formatted using DATETIME_FORMAT(). The whole thing will look like this, using your first desired format (2019-Aug-04) as an example:
DATETIME_FORMAT(DATETIME_PARSE(Date, "MMMM DD, YYYY at hh:mmA"), "YYYY-MMM-DD")
Iâm using Zapier to populate Checkin and Checkout dates for our Airbnb, in Airtable. Iâm using Zapierâs email parser tool to grab data from incoming Airbnb âNew Reservationâ emails, and dump all of these new reservations into my Airtable.
The date in the email is formatted as text: ââJâaânâ â0â9â,â â2â0â2â0â but I would like it to be a date. Or if I need to convert the text field to a date, thatâs fine. My ideal date format would be YYYY-MM-DD
Can I format my âCheck-inâ field to be a formula that accepts the format coming in from Zapier as text?
If not, then Iâm okay with creating another field (as pictured below) called âCheckinâ. I tried this, using the formula
As you can see from the image below, the formula seemed to work on the third row down, but iâm getting an error for all the other rows. @Justin_Barrett do you have any idea what I should do here?
Youâve got one too many Ms in your parse string for the month. MMMM will parse fully-spelled months (January, February, March, etc.), but you need MMM to parse Jan, Feb, Mar, etc. Your formula should be this:
Thanks for the quick response, Justin! I tried the formula you gave me and pushed another sample record through the Zap. The TEXT field was able to bring in the date, as illustrated below. But as you can see⌠the formula field pushed an error when it tried to receive the data: Jan 09, 2020. Any ideas what to try next?
Not sure why itâs failing for you. It works on my end:
Is your formula exactly as I listed above, or did you tweak anything about it before putting it into your formula field?
If thatâs not the case, my only other thought is that there are some special characters that donât display in the {Check-in} field that are causing the formula to fail. You might try this alternative version:
Simply having extra spaces around the text date wonât mess it up (I tried), but perhaps thereâs something else there, and perhaps TRIM() can get rid of it if so.
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âŚ
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 :
Replace 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.