Convert Text to Date

Abraham_Bochner · ‎May 06, 2019

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

Justin_Barrett · ‎May 06, 2019

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:

Abraham_Bochner · ‎May 06, 2019

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?

Justin_Barrett · ‎May 06, 2019

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?

TailoredTouches · ‎Aug 03, 2019

@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”

Justin_Barrett · ‎Aug 04, 2019

Welcome to the community, Callie! :grinning_face_with_big_eyes: 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")

jord8on · ‎Jan 13, 2020

Continuing the discussion from Convert Text to Date:

I also added this discussion and had no replies.

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

DATETIME_FORMAT(DATETIME_PARSE({Check-in}, “MMMM DD, YYYY”), “YYYY-MM-DD”)

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?

Justin_Barrett · ‎Jan 13, 2020

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:

DATETIME_FORMAT(DATETIME_PARSE({Check-in}, "MMM DD, YYYY"), "YYYY-MM-DD")

jord8on · ‎Jan 14, 2020

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?

Justin_Barrett · ‎Jan 14, 2020

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:

DATETIME_FORMAT(DATETIME_PARSE(TRIM({Check-in}), "MMM DD, YYYY"), "YYYY-MM-DD")

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.