Converting 24 hour time to 12 hour time and a REGEX_Replace question

[NJT]

I just wrote my first REGEX expression from scratch, well sort of, with help from all the documentation and posts and YouTube videos from @MarqueeCrew (the link to https://regex101.com/ was great help) and the courage to try from @Joe_Mako but I'm still running into a bit of snag but hopefully I'll sort it out eventually.

After searching the community I found posts regarding converting 12 hour time to 24 hour time but not the other way around. And after I used the 12 to 24 hour documentation to figure out the 24 to 12 path, I still had a client request to format the time a bit outside of normal parameters... they wanted it to be a.m. and p.m. super annoying but with Alteryx and REGEX problem solved. I don't know if anyone else will end up needing something like this but I know I'll forget and be searching for this in the future so this post is for me! The next step is to figure out how to make this all happen in one REGEX tool or formula tool. 

First the conversion from 24 to 12 hour format was easily accomplished with the DateTime Tool and the Alteryx documentation on DateTime Functions when you hit the Specifiers drop down you get an explanation of all the options for date and time formatting in the DateTime Tool and you find the element that gives you the am or pm is the code %P for lower case am/pm and %p for uppercase, but you also have to swap out the usual HH for an %I (upper case i) or if you want to get rid of the leading 0 on the hours you can use %l (lower case L) which gets a little confusing because they look the same in the formula below but you can see in the tool a preview of the output. 

%I:mm %P = 03:00 pm

%l:mm %P =  3:00 pm

Next my REGEX_Replace formula of which I am so proud, which takes the string output from the DateTime Tool where the time was formatted and looks for am or pm and replaces it with a.m. or p.m. I can't even tell you how excited I am about this. It felt like a huge win to finally understand a little REGEX. Although I don't full understand it but regex101 site is definitely helping me along. The problem as those more familiar with REGEX than myself will see right away that I'm replacing am or pm with just a.m. so I still need to figure out how to have it replace just am with a.m. and pm with just p.m. the quick fix for me right now will probably be just two formula tools. 

[SophiaF]

A problem I have also faced! Thanks for sharing!

[ivoller · Accepted solution]

Using capturing groups, I think that  REGEX_Replace([Start Time Formatted], "([ap])m", "$1.m.") will get it.

[NJT]

That did it, my fix was two formula tools and doing the am in one pass and pm in another pass. Thanks for optimizing me! 

     

[NJT]

I can't even say how many times I have had to reference this post it's embarrassing. I need to save expressions since I clearly can't memorize them.