+16From 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
Best answer by kuovonne
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.
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.
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"
)
)
)
+20@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 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.
+20Thanks @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.
+29What 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.
+16Sorry, 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.
+20@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.
+16@Andrey_Kovalev at first I thought that was the solution, but I still can’t format the field as a duration.
+29@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.
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.
+16This 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.
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 )
+29Thanks, 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 )
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.
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.
+16Yes, 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.
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.
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.
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.
+29The 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.
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.
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"
)
)
)
Thanks for that! I didn't realize that regex maintains the original field type/formatting.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.