Convert CSV String to Date/Time

[TylerMay]

I have an issue when trying to convert a date/time from a CSV file (which is imported as a string) to an actual date.  I have tried searching on here but cannot find a solution that fixes my problem (i apologize if i missed a post!).  The format for my dates are as follows:

Dates:

11/14/2016 8:26:38 AM
11/15/2016 9:14:33 AM
11/16/2016 11:27:56 AM
11/16/2016 12:20:29 AM
11/21/2016 1:37:13 PM

I started off by using the DateTime Parse function but it did not work because of the format of the string.  Then i found a great post and macro called BB Date.  This tool worked great except it does not correctly convert a date/time in the 12:00 AM hour.  For some reason, it converts the PM times to the appropriate time (13:37:13 for the last date in the list above) but not 12:00 AM.

Does anyone know how i can easily transform this string into an actual date?

[Philip]

I'm thinking there has to be a better way, but here's one solution. First, it parses the datetime into individual fields, then puts it all back together again in Alteryx's standard datetime format.

[jdunkerley79 · Accepted solution]

Following a similar approach to @Philip, I pulled it apart and plugged it back together.

REGEX tool in parse mode to break into each part.

Then a formula tool to handle AM/PM and merge back into a date

[TylerMay]

---

@jdunkerley79 wrote:

Following a similar approach to @Philip, I pulled it apart and plugged it back together.

REGEX tool in parse mode to break into each part.

Then a formula tool to handle AM/PM and merge back into a date

---

Thank you so much for this!  You have saved me so much time!

[patrick_digan]

@TylerMay I just wanted to add that you should be able to use the DateTimeParse function when 11.0 comes out (in the next few months). They are adding some functionality around the DateTime tools, and I tested your dates with the below formula and it parsed them all correctly.

DateTimeParse([Field2], '%m/%d/%Y %I:%M:%S %p')

Here are the details from the 11.0 Beta: 

The DateTime tool now supports specifying a custom format for converting date/time data to a string, and for converting a string to a date/time format. Dates may include two-digit years and abbreviated months, and no longer require leading zeros on days, months, and time.

I don't often use DateTimeParse but I saw a post by @MichaelCh and realized it would apply to your situation as well.