Converting AM/PM Time to 24 Hours format, in Alteryx

by Henry Mak

As part of Alteryx Weekly Challenge 87, I had to convert times which were in 12 hour format with AMs and PMs into 24 hour format. This was so I could use the DateTimeDiff function. Of course, after finishing that challenge I thought to my self:

Man. That challenge took (unexpectedly) so long.

There must've been a more efficient way to do things...

After doing some Googling I had found that there were two methods in particular that I could have used to convert the AM/PM time into a 24 hour format. In the end, I didn't use either but they might be useful for future reference.

Method 1 - The long way:

One way to do it is to use a Formula tool with the following logic:

Easy? Probably... Fast? Probably not...

Method 2 - The fast way:

Here's some data that I mocked up for this example. As we can see, there is a wide variety of times. Some are in the AM, some PM. Some have a single digit in the hour of the time, some have two.

Side note: Your data may not come like this so you may need to do some cleaning steps beforehand.

In order to convert these into the 24 hours format I used the following formula:

DateTimeParse([Time] + " " + [AM/PM], "%I:%M %P")

To explain some of the less obvious syntax, here is what "%I:%M %P" means in DateTime:

  • %I refers to the hour in the 12 hour format. This goes from 01 to 12
  • %M refers to the minutes. This goes from 00 to 59
  • %P refers to AM or PM
But what if my input time looks a bit different? What if it has dots instead of colons?

Fear no more.

DateTimeParse(Replace(ToString([Time],2),".",":") + " " +[AM/PM],"%I:%M %P")

This formula replaces the dots with the colons.

Tableau Public