Help

Automation to convert text timecode string to a duration field

Solved
Jump to Solution
1229 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Kelly_Vorrasi1
6 - Interface Innovator
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. 

trt.jpg

1 Solution

Accepted Solutions
Sho
11 - Venus
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

 

See Solution in Thread

4 Replies 4

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
11 - Venus
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

 

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

Kelly_Vorrasi1
6 - Interface Innovator
6 - Interface Innovator

These both worked great! Thank you!