Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Convert String (date/time) to 24 hour date/time stamp

tschoeny
5 - Atom

I have a piece of data that looks like this (string format):

started_on

01-JUL-21 09.58.02.755421000 AM

completed_on

01-JUL-21 01.48.46.568084000 PM

 

I am trying to convert it to 24 hour format so i'm able to subtract two values from each other. For example: this should be 3 hours 50 minutes or 230 minutes

5 REPLIES 5
dfurlow
8 - Asteroid

Weekly Challenge 10 may be helpful...

apathetichell
18 - Pollux

datetimeparse(regex_replace([Field1],"(.*)\.(\d+\s)(\w{2})$","$1 $3"),"%d-%b-%y %I.%M.%S %P")

 

this is for formula - new datetime field...

 

You can also use a multifield formula where you convert your field to datetime and use the above with [_currentfield_] replace [Field1]

 

you can then use datetimediff ([row-1:date],[date],"minutes") in a multi-row formula to get the difference.

 

Note - you have to get rid of the units below seconds. I use regex to do this in one command but there are other strategies as well. You also have to use special parameters to tell datetimeparse you are on a 12 hour/am/pm cycles... (%I %P)

atcodedog05
22 - Nova
22 - Nova

Hi @tschoeny 

 

Here is my take on it. Its similar to @apathetichell's approach. I prefer to use formula tool since it's only 2 dates and we can complete all calculations in the same tool.

 

Formula to convert to datetime

datetimeparse(
regex_replace([started_on],"(\.\d+\s)",""),
"%d-%b-%y %I.%M.%S %P")

 

Workflow:

atcodedog05_0-1627830205416.png

 

Hope this helps : )

 

tschoeny
5 - Atom

thank you both!

atcodedog05
22 - Nova
22 - Nova

Happy to help : ) @tschoeny 

Cheers and have a nice day!

Labels