Skip to main content

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. 

Try this formula

VALUE(DATETIME_FORMAT(DATETIME_PARSE(TRT, "HH🇲🇲ss;SS"), "HH"))*3600 + VALUE(DATETIME_FORMAT(DATETIME_PARSE(TRT, "HH🇲🇲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. 

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

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

 

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

These both worked great! Thank you!

Reply


Terms & Conditions