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.
Solved! Go to Solution.
@michaelalexander what do you want your data to look like in the end?
@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!
@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!
Thank you so much for your help!! I appreciate it a lot.
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
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
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...