One of the problems I came across on an Alteryx weekly challenge was the conversion of a string format written as 9:52 AM. This conversion proved challenging as Alteryx does not list the characters needed to switch this to a date format. After finding the solution, I thought I would write this blog to help out others who are starting with Alteryx, and might need some help with date time conversions. The examples in this challenge required the formatting of dates that were set as 9:52 AM, and 2:20PM, and needed a custom format for the DateTime parse tool. All of the date time functions can be found here: https://help.alteryx.com/20213/designer/datetime-functions.

The one that we can use for this example is %I:%M %p. The key to this format is the %p at the end of the formula, as it allows us to convert the time into a 24 Hour clock based on the AM or PM labelling. The formula can be entered in two ways shown below:

This way uses the DateTime parse tool to convert the string fields into a time field that can be read by Alteryx for custom date functions. Remember to write the format as it appears with the : separator, and the space between the AM.

This second way runs the formatting through a formula tool with the DateTimeParse function. In this example, [Enter] is the string field we want to convert, and the formula still needs to include the : and space. This gives us the same result, and I would recommend using the DateTime parse tool as it gives a preview of how the day will look.

Once the string field is converted into our time format, we can now use specific date time formulas listed in the attached link.