nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Date Time

Accepted answers

atcodedog05

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 : )

format and duration.yxmd

All comments

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

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 : )

format and duration.yxmd

tschoeny

thank you both!

atcodedog05

Happy to help : ) @tschoeny

Cheers and have a nice day!

Quick Links

Popular Tags

This months top contributors

atcodedog05 19598

Qiu 15905

binu_acs 15783

MarqueeCrew 13709

apathetichell 13703