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

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

Twitter

LinkedIn

Author:
Henry Mak
Powered by The Information Lab
1st Floor, 25 Watling Street, London, EC4M 9BR
Subscribe
to our Newsletter
Get the lastest news about The Data School and application tips
Subscribe now
© 2025 The Information Lab