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.