community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

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

Highlighted

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.

Pulsar

@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!

Pulsar

@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.

Alteryx
Alteryx

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

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

Alteryx
Alteryx

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...

Asteroid

Gm @KaneG-

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

sorry about the typo.

 

output.PNG      formula.PNG

Labels