String to Date format

How do I convert a string format to Date or vice versa.

I have two sets of data where one has month as 'JAN-21' in string format and other one has month as '01-Jan-21' in date format, problem is in output file where both are merged into a column after changing formats as "%b-%y" but are recognized separately.

Even when I try to make a pivot from the output its showing up differently, tried a lot to convert either of them but no luck.

Common Use Cases

Accepted answers

atcodedog05

Hi @AjaySetty

Interestingly there were some hidden special characters.

I clean it up all unwanted chars using formula REGEX_Replace([Period], "[^\w\-]+", ""). Then i was able to convert to dates.

Workflow:

Hope this helps : )

date parse.yxmd

All comments

atcodedog05

Hi @AjaySetty

Here is how you can do it. You can use formula like below to convert to proper date format.

IF REGEX_Match([Field1], "\u+\-\d+") 
THEN DateTimeParse([Field1],"%b-%y") 
ELSE DateTimeParse([Field1],"%d-%b-%y")  ENDIF

Workflow:

Hope this helps : )

date format.yxmd

Laurap1228

Hi,

I find it's best to get fields into the same format with the same metadata before I merge them. See attached example. Hope this helps!

Dates Example.yxmd

AjaySetty

Actually they are coming in from different inputs and I have managed to make them appear similarly but the format remains a challenge coz even though they are similar (Jan-21) in output one is treated as date and other as string.