Using regex_match for date transformation

[WX]

I have a column called Trip_Start, which is a V_String and coming from an Excel imported file. The values are not well formatted, the are either 

2020-09-28 (correct)

or

28-Sep-2020 (wrong).

I was thinking about the formula tool and decide via regex_match whether there is a letter (-> parse) or no letter (-> just take the original value)

However as you can see on the preview cell, the regex is not signalling any hit and therefore always showing the else part of the statement.

Just to make sure datetimeparse is working as intended, here a formula without previous regex_match.

Any ideas?

[atcodedog05]

Hi @WX ,

I was not able to flag the dates. But instead with use of ISNULL and Dateparse i used below formula to parse the dates

IF !IsNull( DateTimeParse([Date],"%Y-%m-%d")) THEN
DateTimeParse([Date],"%Y-%m-%d") 
ELSEIF !IsNull( DateTimeParse([Date],"%d-%b-%Y")) THEN
DateTimeParse([Date],"%d-%b-%Y")
ELSE Null() ENDIF

Output

Hope this helps : )

If yes please mark the post as solution.

[vizAlter]

Hi @WX — If you want to get the solution through formula, then here it is:

IF Contains(Left([Imported_Date],4), "-") THEN 
 DateTimeFormat(DateTimeParse([Imported_Date], "%d-%b-%Y"), "%Y-%m-%d")
ELSE 
 DateTimeParse([Imported_Date],"%Y-%m-%d") 
ENDIF

[Akash_Mishra]

You can use this formula to achieve this without using regex.

if Substring([Trip_Start],2,1)="-" then DateTimeParse([Trip_Start],"%d-%b-%y") else [Trip_Start] endif

Mark as solve if this solved your issue.

[jdunkerley79 · Accepted solution]

You could do something like:

IF REGEX_Match([Trip_Start],"\d{1,2}-[A-Za-z]{3}-\d{2}(\d{2})?")
THEN DateTimeParse([Trip_Start], "%d-%b-%y")
ELSE [Trip_Start]
ENDIF

The Regex should match 2-Sep-20, 27-Sep-2020 etc

[WX]

Thanks @jdunkerley79. Your solution is perfect and also helped me unterstanding a bit more about regular expressions.

Also thanks to all the other solutions.

[atcodedog05]

Hope you could have accepted other solutions also since it meets the criteria @WX 

Anyways happy to help : )

Cheers and Happy Analyzing : )