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

[tschoeny]

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

[dfurlow]

Weekly Challenge 10 may be helpful...

[apathetichell]

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 · Accepted solution]

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:

Hope this helps : )

[tschoeny]

thank you both!

[atcodedog05]

Happy to help : ) @tschoeny 

Cheers and have a nice day!