Friendly date not properly parsed

[SuperAirtag]

It seem “friendly date” in the following format is not properly being turned into a date field: June 25th 2018 at 1:01 PM. Is this correct, or am I missing something?

[M_k]

Hi @SuperAirtag

I did a small test to show an example of the date features, with and without time. You can see the example base that the friendly date format was showing the correct time and date. You can select any date by spinning the wheel.

Here are some screenshots:

Hope this helps.
Mary

[Justin_Barrett]

Welcome to the community, Bart! :grinning_face_with_big_eyes: Are you trying to take a text field containing dates like that and convert it into a date field? A little more info about what you’re trying to achieve would help.

[SuperAirtag]

Thanks for the replies! I’m importing data through the API, and the dates in the format I posted above, don’t come through.

@M_k I don’t know where these screenshots comes from, I don’t see a wheel to spin anywhere… Unfortunately this forum does not allow me to post any links or screenshots…

[SuperAirtag]

When importing through the API, the date fields are blank. When switching the date field to a single text field, the dates come through, but these are not interpreted as actual dates. Converting the column to the actual date type using friendly format empties all cells. Below a cut and paste of the raw dates:

October 24th 2017 at 2:36 PM
March 22nd 2018 at 2:30 PM
April 2nd 2018 at 3:04 PM
April 26th 2018 at 2:51 PM
June 25th 2018 at 1:01 PM
October 10th 2018 at 3:45 PM
October 11th 2018 at 3:38 PM
October 22nd 2018 at 10:48 AM
October 23rd 2018 at 9:29 AM
October 31st 2018 at 2:28 PM

[Justin_Barrett]

SuperAirtag:

Converting the column to the actual date type using friendly format empties all cells.

Correct. When switching a field from one type to another, the formatting options in the target type (like the “friendly” display format for date fields) only apply after conversion. They won’t have any impact on the conversion itself. The fact that Airtable blanks out the fields means that it’s not able to automatically convert the text into a date without outside assistance.

Even though it’s an extra step, I suggest adding a formula field and using DATETIME_PARSE() to parse the text into a proper datetime object. Here’s a format that works with your examples:

DATETIME_PARSE(Date, "MMMM Do YYYY at h:mm A")

Oh, and you’re not seeing the spinning wheel thing because the screenshots from @M_k are of the iPad interface.