# Duration TEXT to correct formating

Topic Labels: Formulas
Solved
242 4
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

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

Accepted Solutions
6 - Interface Innovator

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 4
6 - Interface Innovator

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

5 - Automation Enthusiast

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

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

6 - Interface Innovator

Hi,

Sorry, I forgot this one in my previous answer.

Here is the formula:

SEARCH("/",values)

Regards,
Pascal
5 - Automation Enthusiast

Thank you so much!!