Extracting date from string

[IndiFit_Team]

Hi,
I need to extract the time and date from strings such as this one and add them as a separate cell in the record:
“Thu 7/06 9:00am - Vinyasa Yoga @ Millennium Park”

I’m trying this but it’s returning “-2”: MID(my_string, FIND("/",my_string), - 2)
Also tried this to get the date and time up until the “:”: MID({Class Title}, FIND("/",{Class Title},0), -2,FIND(":",{Class Title}))
[my_string is actually the field where the string is hosted]

What would you recommend to extract the date? And what format would you recommend to have the time & date (e.g. both in one cell or separate for time and date).

Many thanks for your help :slightly_smiling_face:

[Justin_Barrett]

Welcome to the community, @IndiFit_Team! :grinning_face_with_big_eyes: How consistent are the event details you’re getting? If everything has the date and time, followed by a hyphen, and then the name and other details, this should be fairly easy to extract. However, the methods you’re using are trying to pick out one specific piece instead of parsing the whole thing. I suggest the latter approach, treating the date/time combo as a single unit, and using Airtable’s parsing capabilities to let it figure out how to read it.

First figure out how to get everything before the hyphen (more precisely the space-hyphen-space combo). That can be done with FIND(). I put your example string into the {Name} field for this test:

FIND(" - ", Name)

That gives me a value of 16, which I can then use to extract characters from the start of the string using the LEFT() function. By subtracting 1 from that value, I can get the first 15 characters in this case:

LEFT(Name, FIND(" - ", Name) - 1)

That leaves just the date and time:

Thu 7/06 9:00am

Now we wrap DATETIME_PARSE() around that result to give us an actual datetime value:

DATETIME_PARSE(LEFT(Name, FIND(" - ", Name) - 1), "ddd M/DD h:mma")

[IndiFit_Team]

Thank you so much @Justin_Barrett ! It should work as you explain here, but for some reason the FIND does not find the “-” .
All the strings will have the same format (just the old data is still not in the “Thu 7/06 9:00am - Vinyasa Yoga @ Millennium Park” format. The new data is from roe 399-403)

Do you know why it returns 0 in the FIND function?

[Justin_Barrett]

IndiFit_Team:

It should work as you explain here, but for some reason the FIND does not find the “-” .

Not all hyphens are created equal. There are three different types: - – —. The type of hyphen in your field data is probably not a match for the default hyphen that most people type on the keyboard. The best way to ensure a perfect match is to copy one from your data and paste it into the formula in the FIND() portion.

[IndiFit_Team]

Thanks, I have tried copy pasting the " - " into the formula (also tried with different “-” from different cells) but it’s still not returning the number. Could it be that FIND doesn’t work with data { inside of these parenthesis} ?

[Justin_Barrett]

Sorry. I overlooked a clue from your screenshot. You’re pulling that info from a lookup field, and lookup fields are finicky beasts. Most of the time they return arrays—containers of several values—not individual values (strings, numbers, dates, etc.), and FIND() won’t work with an array. You’ll need to convert its contents to a string before FIND() will work reliably. That can be done by concatenating the field value with an empty string — {Class Title} & "" — and this will need to be done for each instance of that lookup field in the formula.

Try this formula instead:

DATETIME_PARSE(LEFT({Class Title} & "", FIND(" - ", {Class Title} & "") - 1), "ddd M/DD h:mma")

[IndiFit_Team]

we are almost there @Justin_Barrett ! Now for some reason the “am” times are not showing up.
I’ve tried duplicating the field and adding " " to it at the source

the good news is that FIND works when I duplicate the class title field with the formula {Class Title} but when I apply DATETIME_PARSE(LEFT({Field 17}, FIND(" - ",{Field 17}) - 1), “ddd M/DD h:mma”) it also doesn’t show the AM times.

!

[Justin_Barrett · Accepted solution]

I just tested those same items (slightly modified) on my end using the same formula, and they all work fine:

There must be something in the data that’s throwing off the formula. In the last two, I’m pretty sure it’s because you don’t have a space after the hyphen. When I replicate that, I get the same error:

I’m not sure what’s up with the first of those three. That aside, I modified the formula slightly to only look for the hyphen, which is probably a safer check anyway. This works even when there’s no trailing space after the hyphen:

DATETIME_PARSE(TRIM(LEFT({Class Title} & "", FIND("-", {Class Title} & "") - 1)), "ddd M/DD h:mma")

[IndiFit_Team]

Justin_Barrett:

DATETIME_PARSE(TRIM(LEFT({Class Title} & "", FIND("-", {Class Title} & "") - 1)), "ddd M/DD h:mma")

Thank you SO SO much! This worked… I’m sending you a virtual cake as a BIG thank you for your amazing help :birthday:

( ```
DATETIME_PARSE(TRIM(LEFT({Class Title} & “”, FIND("-", {Class Title} & “”) - 1)), “ddd M/DD h:mma”)