Extract Dates From a List of data

[Chris_Jeal]

Hi Team,

I wonder if you would know how to extract the dates from this list of data?

The list has been generated via an Enumlist of Google Calendar IDs. 

I've looked at the EXTRACT() functions but can't work this one out.

Any ideas?

Best,

Chris

[Suvrutt_Gurjar]

Is the string length going to be constant?

Also will the data be available as a list of multiple strings or just one text string?

[Chris_Jeal]

@Suvrutt_Gurjar 

Thanks for the reply, I think that the list will be of multiple strings with constant lengths.

[Suvrutt_Gurjar · Accepted solution]

Please try

SPLIT(

SUBSTITUTE(EXTRACTHASHTAGS(SUBSTITUTE(SUBSTITUTE(

LIST(

1e1h1k2skskk_20220620T070000Z ,

1e1h1k2skskk_20220622T070000Z ,

1e1h1k2skskk_20220624T070000Z

)

,"_","#"),"T"," ")),"#",""),

",")

Here the LIST() part highlighted in orange is the LIST() string you have shared. You will need to insert that list in place of orange highlighted list.

Assumption: Each string has "_"  before the date and "T" after the date at the fixed place.

[Chris_Jeal]

@Suvrutt_Gurjar 

many thanks for that, it worked a treat.

I'm wondering, would you know now how to count how many times "2022" appears in this list?

[Suvrutt_Gurjar]

Please try below mentioned expression.

Also would you mind marking it as a solution, so that anyone looking for similar requirement in future can find it in easier manner.

COUNT(EXTRACTHASHTAGS(SUBSTITUTE(SPLIT(SUBSTITUTE(EXTRACTHASHTAGS(SUBSTITUTE(SUBSTITUTE(

LIST(

1e1h1k2skskk_20220620T070000Z ,

1e1h1k2skskk_20220622T070000Z ,

1e1h1k2skskk_20220624T070000Z

)

,"_","#"),"T"," ")),"#",""),","),"2022","#")))

The search string "2022" is highlighted in blue. Please suitably replace it by a field /different year for searching a different year etc.

Note: The solution assumes there are no "#" (Hashtags) in the input strings. If there could be then we will need to use maybe extract mentions with "@"

[Chris_Jeal]

thanks again @Suvrutt_Gurjar ,

Works really well when selecting the year explicitly, struggles with USERSETTINGS("Year") though for some reason.

[Suvrutt_Gurjar]

Please try by having Usersettings(Year) column type as "Text" or by wrapping UserSettings(Year) with TEXT()

TEXT(Usersettings(Year) )