Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Nov 14, 2022 05:52 AM
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
Solved! Go to Solution.
Nov 24, 2022 12:00 PM
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"
)
)
)
Nov 14, 2022 07:00 AM
@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))))
Nov 14, 2022 08:57 AM
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.
Nov 14, 2022 10:42 AM
@Ricardo Do you keep Time as a text? In my solution Time is of Date format.
Nov 14, 2022 10:46 AM
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.
Nov 14, 2022 03:41 PM
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.
Nov 22, 2022 02:08 AM
@kuovonne @Andrey_Kovalev have you giot any other ideas, I’m not getting there. :frowning:
Nov 22, 2022 03:41 AM
@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.
Nov 22, 2022 05:17 AM
@Andrey_Kovalev at first I thought that was the solution, but I still can’t format the field as a duration.
Nov 22, 2022 05:22 AM
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.