Formatting a text string before transforming it to a date. Formula tool?

[tsnouffer]

I'm prepping a data source that has a text field for data formatted generally as January 9, 2016. The Date/Time Parse tool supports a format of Month dd, yyyy. So when I run it any of the dates with a single-digit day comes back as Null. 

How can I adjust my original string so that something like January 9, 2016 is changed to January 09, 2016 and is then supported by the Date/Time Parse tool?

Thanks

[JordanB · Accepted solution]

Hi @tsnouffer

I would highly recommend looking at the Parse dates macro created by @AlexKo. This macro can do all the leg work for you in regards to the questions you had. You can simply type the data format you have an it will convert it to Alteryx format. 

Alternatively you could look at parsing out your date into different fields and when you have the numerics in their own field look at using the length function and if it equals 1 you would look at the padleft function and add a 0. You would then need to concatenate the fields back and use the date time tool. 

Best,

Jordan Barker

Solutions Consultant 

[tsnouffer]

Thanks for pointing me in the right direction. I'm going to spend some time to digest the macro. I parsed out my fields, padded the date and then concatenated them as a solution for now. I lilke that there is more than one way to skin a cat!