Parse inconsistent date formats from string

[Bunbunrachel]

Hi all, 

I have the following datasets:

06:00pm CDT - 03 January 2022

07:00pm CDT: 03/04/2022

09:23pm CDT - 04th Jan 2022

I will like to parse into date format:

03/01/2022

03/04/2022

04/01/2022

Appreciate any help on the above!

[Yoshiro_Fujimori · Accepted solution]

Hi @Bunbunrachel ,

You can distinguish the input pattern with RegEx_Match() function.

Then you can use DateTimeParse() function for each input pattern.

I hope this works.

Workflow

Formula tool expressions

Date = 

IF     REGEX_Match([Data], ".*CDT - \d{2} \w+ \d{4}")
  THEN DateTimeParse(REGEX_Replace([Data], ".*CDT - (\d{2} \w+ \d{4})", "$1"),"%d %B %Y")
ELSEIF REGEX_Match([Data], ".*CDT: \d{2}\/\d{2}\/\d{4}")
  THEN DateTimeParse(REGEX_Replace([Data], ".*CDT: (\d{2}\/\d{2}\/\d{4})", "$1"), "%d/%m/%Y")
ELSEIF REGEX_Match([Data], ".*CDT - \d{2}th \w+ \d{4}")
  THEN DateTimeParse(REGEX_Replace([Data], ".*CDT - (\d{2})th( \w+ \d{4})", "$1$2"), "%d %b %Y")
ELSE Null()
ENDIF

Output = DateTimeFormat([Date],"%d/%m/%Y")

Output

[Bunbunrachel]

Thank you @yoshiro_fujimori

[Bunbunrachel]

Hi @yoshiro_fujimori, can I ask:

1. What does “.*” means?

2. Instead of including “CDT” and “-“ - is it possible to exclude since there are multiple permutations of values.

Thanks again for your help!

[Yoshiro_Fujimori]

Hi @Bunbunrachel ,

1. What does “.*” means?

It is a Regular Expression meaning "0 or more occurrence of any character".

As RegEx is a profound topic in itself, I recommend you watch the Interactive Lesson "Using RegEx in Expressions".

2. Instead of including “CDT” and “-“ - is it possible to exclude since there are multiple permutations of values.

You can relax the matching condition by calibrating the RegEx as necessary.

For example, if you want to match with any 3 uppercase alphabets, you replace "CDT" with "\u{3}" in the expression.

I hope this helps.