Re: Converting string to duration field (text to seconds)

Ricardo · ‎Nov 14, 2022

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

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

kuovonne · ‎Nov 24, 2022

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"
    )
  )
)

See Solution in Thread

Andrey_Kovalev · ‎Nov 14, 2022

@Ricardo Try this:

IF({Time},IF(DATETIME_DIFF({Time},NOW(),'days') = 0, CONCATENATE(MOD(DATETIME_DIFF({Time},NOW(),'hours'),24),':',RIGHT("0"&MOD(DATETIME_DIFF({Time},NOW(),'minutes'),60),2)), CONCATENATE(DATETIME_DIFF({Time},NOW(),'days'),' days ',MOD(DATETIME_DIFF({Time},NOW(),'hours'),24),':',RIGHT("0"&MOD(DATETIME_DIFF({Time},NOW(),'minutes'),60),2))))

Ricardo · ‎Nov 14, 2022

Thanks @Andrey_Kovalev, unfortunately this throws an error for me.

Any of the MOD() throws an error, the DATETIME_DIFF by itself results in a NaN.

I thin this is because it doesn’t recognize the string.

Andrey_Kovalev · ‎Nov 14, 2022

@Ricardo Do you keep Time as a text? In my solution Time is of Date format.

kuovonne · ‎Nov 14, 2022

What is the original field type of the value being looked up? Can you include a screen shot?
Depending on the field type, you might be better off using a rollup field instead of a lookup field.

Lookup fields are one of the more confusing field types to use in formulas. Often the values are arrays (lists) and not text strings.

Ricardo · ‎Nov 14, 2022

Sorry, maybe my description is misleading. The lookup field holds an array / multiple duration and I’m using REGEXEXTRACT to extract one as a text string. This relates to what both of you have been mentioning.

Ricardo · ‎Nov 22, 2022

@kuovonne @Andrey_Kovalev have you giot any other ideas, I’m not getting there. :frowning:

Andrey_Kovalev · ‎Nov 22, 2022

@Ricardo Try this formula field. It takes a string representation of your time and converts to time format:

IF(strTime, DATETIME_FORMAT(DATEADD(DATEADD(TODAY(),LEFT(strTime,FIND(":",strTime)-1)+0,'hours'),RIGHT(strTime, 2)+0,'minutes'),'H:mm'))

I hope this is what you expected to get.

PS: Don’t forget to replace strTime to the required field name.

Ricardo · ‎Nov 22, 2022

@Andrey_Kovalev at first I thought that was the solution, but I still can’t format the field as a duration.

kuovonne · ‎Nov 22, 2022

This still results in a text string. Plus, what if a duration is over 12 or 24 hours?

If the original field being looked up is a duration, I think converting it to text and then back to a number is counterproductive. Can you use a conditional rollup/lookup to show only one value? If you cannot directly use a conditional rollup, how about a back and forth system of rollups to identify the first value and roll that up? There are several posts on this forum for identifying the first date. Something similar could be done here. Then you don’t have to do data type conversions.