Help

Airtable Cobuilder is here! Learn more about our new no-code app creation feature, powered by AI on the Airtable Academy

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

Solved
Jump to Solution
4224 5
cancel
Showing results for 
Search instead for 
Did you mean: 
Ricardo
7 - App Architect
7 - App Architect

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
1 Solution

Accepted Solutions

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

14 Replies 14
Andrey_Kovalev
8 - Airtable Astronomer
8 - Airtable Astronomer

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

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.

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

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
7 - App Architect
7 - App Architect

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.

image

Ricardo
7 - App Architect
7 - App Architect

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

@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
7 - App Architect
7 - App Architect

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

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.