# Automation to convert text timecode string to a duration field

Solved
794 4
cancel
Showing results for
Did you mean:
6 - Interface Innovator

Hi All,

I'm trying to use a linked record to pull up the TRT of an asset that's listed out as a full timecode, extract just the hours and minutes and put that into a duration field. I've gotten so far as to pull the hours and minutes but when I set the automation to fill the duration, because it's not reading the text as seconds, it's coming in with the wrong duration. I haven't been able to get datetime_parse to convert the text to time. I can sense there's an easier way to do this that I'm just not landing on but nothing I've tried has worked.

1 Solution

Accepted Solutions
11 - Venus

Databaser is the solution.
This formula can also be written this way.

``HOUR(DATETIME_PARSE(TRT, "HH:mm:ss;SS")) * 3600 + MINUTE(DATETIME_PARSE(TRT, "HH:mm:ss;SS")) * 60``

4 Replies 4
12 - Earth

Try this formula

``VALUE(DATETIME_FORMAT(DATETIME_PARSE(TRT, "HH:mm:ss;SS"), "HH"))*3600 + VALUE(DATETIME_FORMAT(DATETIME_PARSE(TRT, "HH:mm:ss;SS"), "mm"))*60``

This takes the text value from your {TRT as text} and puts it in a date form. Then extracts the hours "HH" (24 hour clock; use "hh" for 12 hour clock), uses "value" to transfer it to a number and multiplies it with 3600 to convert it to seconds. Same principle for the minutes "mm".

Then use an automation of just copy past into your duration field.

11 - Venus

Databaser is the solution.
This formula can also be written this way.

``HOUR(DATETIME_PARSE(TRT, "HH:mm:ss;SS")) * 3600 + MINUTE(DATETIME_PARSE(TRT, "HH:mm:ss;SS")) * 60``

12 - Earth

Yes, that seems like a nice improvement of my formula 👍

6 - Interface Innovator

These both worked great! Thank you!