Automation to convert text timecode string to a duration field

[Kelly_Vorrasi1]

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. 

[Databaser]

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. 

[Sho · Accepted solution]

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]

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

[Kelly_Vorrasi1]

These both worked great! Thank you!