Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

michaelalexander
6 - Meteoroid

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.

8 REPLIES 8
Kenda
16 - Nebula
16 - Nebula

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

michaelalexander
6 - Meteoroid

@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
16 - Nebula
16 - Nebula

@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
6 - Meteoroid

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

KaneG
Alteryx Alumni (Retired)

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
8 - Asteroid

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

 

timeconv.PNG

KaneG
Alteryx Alumni (Retired)

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
8 - Asteroid

Gm @KaneG-

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

sorry about the typo.

 

output.PNG      formula.PNG

Labels