Skip to main content
Solved

Duration TEXT to correct formating

  • June 25, 2024
  • 4 replies
  • 37 views
M
Forum|alt.badge.img+4

can you please help me convert this to the proper duration format? OR maybe into Seconds.

I have the formula below:

VALUE(LEFT(SUBSTITUTE({Fulfillment Time}, ' minutes and ', ':'), FIND(' seconds', SUBSTITUTE({Fulfillment Time}, ' minutes and ', ':')) - 1))
 
But it will return an incorrect number or 0 seconds if the Fulfillment Time would have "1 minute" or "1 second" since the formula above only captures "minutes" and "seconds". I've been trying to find solution for days now.
Temporarily, I'm cleaning the data thru Google sheet changing the minute to minutes and second to seconds. Also adding 0 seconds if the data only says (sample) "3 minutes" and adding 0 minutes if the data only says (sample) "45 seconds". PLEASE HELP

Best answer by Pascal_Gallais-

Hello,

Here is a possible solution.

Here are the Fulfillment Time values used for this test, and the duration in seconds obtained for each line:

I created 4 formula fields to get the final result.

-1- Values

Formula is

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE({Fulfillment Time},"minute","/"),"second",""),"s",""),"and","")," ","")
to get this result
 

-2- Minute_value

IF(Separator_Pos>0,LEFT(values,Separator_Pos-1))
to get this result

-3- Second_value

IF(Separator_Pos>0,RIGHT(values,LEN(values)-Separator_Pos),values)
to get this result

-4- duration_seconds

This formula to get total duration in seconds

second_value + ((Minute_value+0) * 60)
 
Regards
 
Pascal

 

4 replies

Pascal_Gallais-
Forum|alt.badge.img+21

Hello,

Here is a possible solution.

Here are the Fulfillment Time values used for this test, and the duration in seconds obtained for each line:

I created 4 formula fields to get the final result.

-1- Values

Formula is

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE({Fulfillment Time},"minute","/"),"second",""),"s",""),"and","")," ","")
to get this result
 

-2- Minute_value

IF(Separator_Pos>0,LEFT(values,Separator_Pos-1))
to get this result

-3- Second_value

IF(Separator_Pos>0,RIGHT(values,LEN(values)-Separator_Pos),values)
to get this result

-4- duration_seconds

This formula to get total duration in seconds

second_value + ((Minute_value+0) * 60)
 
Regards
 
Pascal

 

M
M
Forum|alt.badge.img+4
  • mikeeTHINK
  • Author
  • New Participant
  • June 25, 2024

Hello,

Here is a possible solution.

Here are the Fulfillment Time values used for this test, and the duration in seconds obtained for each line:

I created 4 formula fields to get the final result.

-1- Values

Formula is

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE({Fulfillment Time},"minute","/"),"second",""),"s",""),"and","")," ","")
to get this result
 

-2- Minute_value

IF(Separator_Pos>0,LEFT(values,Separator_Pos-1))
to get this result

-3- Second_value

IF(Separator_Pos>0,RIGHT(values,LEN(values)-Separator_Pos),values)
to get this result

-4- duration_seconds

This formula to get total duration in seconds

second_value + ((Minute_value+0) * 60)
 
Regards
 
Pascal

 


Hi Pascal! Thank you so much for this! I really appreciate it.

Quick question. How can I formulate the 'Separator_Pos'?

Pascal_Gallais-
Forum|alt.badge.img+21

Hi,

Sorry, I forgot this one in my previous answer.

Here is the formula:

 

SEARCH("/",values)
 
Regards,
Pascal
M
M
Forum|alt.badge.img+4
  • mikeeTHINK
  • Author
  • New Participant
  • June 26, 2024

Hi,

Sorry, I forgot this one in my previous answer.

Here is the formula:

 

SEARCH("/",values)
 
Regards,
Pascal

Thank you so much!!

Terms & ConditionsAccessibility statement