Automation to convert text timecode string to a duration field

Question

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.

Best answer by Sho

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

Sho · Accepted Answer

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

Databaser · Answer

Try this formulaVALUE(DATETIME_FORMAT(DATETIME_PARSE(TRT, "HH:mm:ss;SS"), "HH"))*3600 + VALUE(DATETIME_FORMAT(DATETIME_PARSE(TRT, "HH:mm:ss;SS"), "mm"))*60This 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.

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded