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?
Solucionado! Ir para Solução.
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.
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
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.
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.
Hope you could have accepted other solutions also since it meets the criteria @WX
Anyways happy to help : )
Cheers and Happy Analyzing : )