Converting string to duration field (text to seconds)

[Ricardo]

From a look-up field array, I’m extracting a duration h:mm, which I’m trying to convert to a duration field.

The time comes out alright in a human readable format, e.g. 2:59, 4:24, 6:00, but I’m struggling to convert the string into a duration formatted field via formula.

I tried using a few formula

1. IF(Time,DATETIME_FORMAT(DATEADD(TODAY(),Time&“”,‘s’),‘H:mm’)) = always 0:00.
2. IF(Time,DATETIME_FORMAT(DATETIME_PARSE({Time},‘H:mm’),‘H:mm’)) = still no formatting

[Ricardo]

Thanks, I suppose you refer to this setup?

In the given case, I always want to get the first value in the array, but there’s no logical order as in min/max value, which I think was used to determine the first/last dates?

Using the search I came across this post and am using this formula to get the first item:

LEFT( ARRAYJOIN( {my_array} )&',', FIND( ',', {my_array}&',' )-1 )

[kuovonne]

Ricardo:

I always want to get the first value in the array, but there’s no logical order as in min/max value, which I think was used to determine the first/last dates?

Yes, that is one system. You could use a created time field to identify the first date, which is probably the first linked record. But if there really is no logical order to the durations, why does the first one even matter.

Ricardo:

using this formula to get the first item:

There are many ways to do thing in code. One way is to get the first item as a string, parse the hours and minutes separately, use VALUE() to convert the individual hours and minutes to numbers. Then multiply / add to get seconds, which you then format as a duration. But I wouldn’t do it that way. I would find out out to uniquely identify the one duration I wanted and use a conditional rollup.

[Ricardo]

kuovonne:

But if there really is no logical order to the durations, why does the first one even matter.

The order is user arranged, dragging the linked record up or down.

That’s probably going to much into detail… but I’m a dealing with a junction table called Itinerary, where Trips and Days are related with each other.

• Multiple days combined form the itinerary of a Trip.
• The Days themselves are related to another table called Activities.
• A Day may have multiple Activity options arranged by a human in subjective order.
• For Day A, Activity A may be considered first, for Day B, Activity A comes second…

kuovonne:

I would find out out to uniquely identify the one duration I wanted and use a conditional rollup.

Other than adding yet another junction table to determine the order of activities related to a day, which I was trying to avoid to reduce complexity for the users, I cannot come up with ways to use a conditional rollup.

[kuovonne · Accepted solution]

Ricardo:

The order is user arranged, dragging the linked record up or down.

Okay. Thank you for the explanation.

This formula assumes that the original field is a duration field. Be sure to set the formatting options for the formula field to display the number as a duration.

IF(
  {Time},
  VALUE(
    REGEX_EXTRACT(
      ARRAYJOIN(Time, ","),
      "\\d+"
    )
  )
)

If you want a formula for {Time2} …

IF(
  COUNT({Time}) >= 2,
  VALUE(
    REGEX_REPLACE(
      ARRAYJOIN(Time, ","),
      "(\\d+,)(\\d+).*",
      "$2"
    )
  )
)

{Time3}

IF(
  COUNT({Time}) >= 3,
  VALUE(
    REGEX_REPLACE(
      ARRAYJOIN(Time, ","),
      "(\\d+,){2}(\\d+).*",
      "$2"
    )
  )
)

{Time4}

IF(
  COUNT({Time}) >= 4,
  VALUE(
    REGEX_REPLACE(
      ARRAYJOIN(Time, ","),
      "(\\d+,){3}(\\d+).*",
      "$2"
    )
  )
)

[Ricardo]

Thanks for that! I didn't realize that regex maintains the original field type/formatting.