Having completed Alteryx challenge 106 recently, I thought I'd make a small blog in giving a small tip in formatting time only in Alteryx.
When solving this change, my approach was to simply calculate the difference between the times as a fundamental aspect. So essentially a datetimediff function would be the way to go at some point. However, the times given were in string type and in AM/PM format.
I needed them to be in 24 hour format as well as time type in order for the datetimediff function to work.
So I simply used a formula tool to achieve the end result. A datetimeparse function is what will be used and the following syntax is as shown:
Syntax definitions:
%I --> Hour in 12 clock - 01 to 12. This is used rather than %H which is hour in 24 clock - 00 to 23 since we have AM/PM in our time field. Having AM/PM does not make it compatible with %H.
%M --> For minutes - 00 to 59.
%P --> AM/PM. This is case blind so AM/PM could be lowercase or mix of lowercase and uppercase so it doesn't matter. %P can also be uppercase or lowercase in the formula and it still won't matter as it is case blind. This is used because of it must follow after %I.
As a result you should then end up with the result as shown above. However, what if the time and am/PM are in two different fields?
Not much of an issue as the syntax in the formula can be modified by simply adding/concatenating them:
As mentioned the %p is lowercase and it still doesn't matter as it is case blind.
Note:
Ensure the data type is set to time in the formula tool if you're creating a new column. However, if you're formatting a current time field then put a select tool after the formula to change it to a time type. This is because it is already set as a string or whatever data type and can't be changed within the formula tool.
Hope this small blog helps in formatting time given in string AM/PM format :)