Convert V_String Date/Time from WEEKDAY, MONTH DD, YEAR HH:MM format

[ddiesel]

What's the best approach to convert dates in the following format to Alteryx format?

DateTime

Friday, March 09, 2018 4:44 PM

Thursday, March 08, 2018 3:38 PM

Wednesday, March 07, 2018 10:45 PM

Tuesday, March 06, 2018 2:36 AM

Monday, March 05, 2018 9:07 PM

Right now I'm parsing by \s and then piecing it back together into YYYY-MM-DD HH:MM format after adding 12 hours to PM times.

I know there's a more sophisticated way... What's the best approach?

[danrh]

I would think you'd be able to do this in one step, but I can't seem to get it right.  I'd use the DateTime tool to parse it initially, then a formula to add the 12 hours like you're already doing:

[MarqueeCrew · Accepted solution]

@ddiesel,

I'd like to see that the format %X would do the trick inside of a Parse, but here's how I would do it:

IF RIGHT([Date],2)="AM" THEN
DateTimeParse(Regex_Replace([Date],"(.*)|s[AP]m",'$1'),"%A,%B %d, %Y %H:%M")
ELSEIF
RIGHT([Date],2)="PM" THEN
DateTimeAdd(DateTimeParse(Regex_Replace([Date],"(.*)|s[AP]m",'$1'),"%A,%B %d, %Y %H:%M"),12,"hours")
Else
Null()
ENDIF

Cheers,

Mark

[ddiesel]

Thanks @danrh and @MarqueeCrew

Both suggestions get me close and are more efficient than my 5 tool workaround. Now I just need to adjust for the 12:00 PM hour. 12:00 noon is reading as 12:00 midnight. I'm going to play around with it but reply back if you have a good solution. Thanks!

Sent	DateTime
Saturday, March 10, 2018 12:33 PM	3/11/2018 0:33
Saturday, March 10, 2018 2:32 PM	3/10/2018 14:32
Saturday, March 10, 2018 1:58 PM	3/10/2018 13:58
Saturday, March 10, 2018 3:59 AM	3/10/2018 3:59
Saturday, March 10, 2018 3:14 AM	3/10/2018 3:14
Friday, March 09, 2018 12:58 PM	3/10/2018 0:58

[danrh · Accepted solution]

I'd probably add a condition in the formula tool to check if the hour is either noon or midnight and then re-adjusts the time:

DateTimeAdd([DateTime_Out],
IF Right([DateTime], 2)='PM'
THEN 12 ELSE 0 ENDIF +
Switch(DateTimeHour([DateTime_Out]),0,0,12,12,-12)
,'hours')

[ddiesel]

@danrh Thank you! I got it working yesterday with that same type of logic:

IF [DateTime3]="00" THEN DateTimeAdd([DateTime],-12,"hours") ELSE [DateTime] ENDIF

Again, your solution saves me a step though.

@danrh @MarqueeCrew ... thank you both! Between both your replies it's working well!