Converting Time (5:30 P.M.) to Alteryx Time Format

[michaelalexander]

Hey All, 

I just received a data set that's not in the typical excel format I'm used to converting. Any ideas would be greatly appreciated! Thanks so much to this community!

Original (String):

5:30 P.M.
9:00 P.M.
9:00 P.M.
9:00 P.M.
9:00 P.M.
12:30 P.M.
12:00 P.M.

[Kenda]

@michaelalexander what do you want your data to look like in the end?

[michaelalexander]

@BarnesK I don't really have a preference, just as long as it's in a non-string format that Alteryx can recognize as a time. Later in the workflow I will have to add duration to Start Times and (from what little I know) I need to get it in a proper Date/Time format first. Thank you so much for following-up!

[Kenda · Accepted solution]

@michaelalexander I put together a small workflow that should help. I first separate the "P.M." & "A.M." part into its own column. I then use the DateTime tool to convert the field to a time format. I then use the Formula tool to convert it to military time so that you can add and subtract using DateTimeAdd(dt,i,u) in the future. Hope this helps!

[michaelalexander]

Thank you so much for your help!! I appreciate it a lot.

[KaneG]

A little known tip that might help in these situations is that %X is the Time in hh:mm:ss AM/PM and so if you have the time in that format, DateTimeParse([Time],'%X') will convert it to iso 24hr time.

Reference: https://help.alteryx.com/11.5/index.htm#Reference/DateTimeFunctions.htm#Format

[MD2050]

Hello @KaneG- 

I tried using the tip and i am getting following error .

My [DateOriginated] and [LastModifiedOn] Fields are strings in "MM:DD:YYYY HH:MM:SS AM/PM" format. Will really appreciate your help. Thank you.

ConvError: Formula (26): DATETIMEPARSE: Cannot convert "10/3/2018 6:00:00 AM" to a date/time with format "%X": Expected separator ':': 10/3/2018 6:00:00 AM

[KaneG]

Hi @MD2050,

%X is only for the time and so in order to convert 10/3/2018 6:00:00 AM you would need to use %m/%d/%Y %X as the format.

Note: you have mentioned "MM:DD:YYYY HH:MM:SS AM/PM" but the example in the error is "MM/%d/YYYY HH:MM:SS AM/PM" with slashes and allowing the one digit for the day...

[MD2050]

Gm @KaneG-

The trick worked wonderfully- Thank you very much for your help.

sorry about the typo.