Using regex_match for date transformation

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)

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?

Regex

Date Time

Accepted answers

jdunkerley79

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

All comments

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.

Date.yxmd

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

Convert Date Formats.yxmd

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

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

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703