String to Date format

[AjaySetty]

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.

[AjaySetty]

@HomesickSurfer 

If you see the error screenshot above, there are some characters before and after the month which is not letting conversion so this wont work.

Also these characters are not visible either in spreadsheet or Alteryx when input, so challenging to get this right.

[HomesickSurfer]

Hi @AjaySetty 

Can you share a larger dataset that I can test?

[atcodedog05]

Hi @AjaySetty 

Does your excel have PI data masking 🤔 which is creating an issue?

[AjaySetty]

@HomesickSurfer  here is the extract for you to test the date formats.

[atcodedog05 · Accepted solution]

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

[AjaySetty]

Thanks so much @atcodedog05 this is beautiful as I was struggling with those hidden characters, still learning regex formulas.

[atcodedog05]

Happy to help : ) @AjaySetty 

Cheers and have a nice day!