Help

Friendly date not properly parsed

2009 5
cancel
Showing results for 
Search instead for 
Did you mean: 
SuperAirtag
5 - Automation Enthusiast
5 - Automation Enthusiast

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?

5 Replies 5
M_k
11 - Venus
11 - Venus

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:

2871EC75-FE77-4EED-9B0A-6C18A05561F0.png
4CF3E8A9-7DCB-4006-9641-C6F592F5ED23.png
D617AD96-3003-4D7E-8E9F-AC59E706CBEA.png
6E0EFB60-743C-4038-859B-821F62A81237.png

Hope this helps.
Mary

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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…

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

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")

40%20PM

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