Help

Duration TEXT to correct formating

Topic Labels: Formulas
Solved
Jump to Solution
242 4
cancel
Showing results for 
Search instead for 
Did you mean: 
mikeeTHINK
5 - Automation Enthusiast
5 - Automation Enthusiast

mikeeTHINK_0-1719313700795.png

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

Accepted Solutions
Pascal_Gallais-
6 - Interface Innovator
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:

Pascal_Gallais_0-1719319688612.png

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
 
Pascal_Gallais_1-1719319846035.png

-2- Minute_value

IF(Separator_Pos>0,LEFT(values,Separator_Pos-1))
to get this result
Pascal_Gallais_2-1719319916138.png

-3- Second_value

IF(Separator_Pos>0,RIGHT(values,LEN(values)-Separator_Pos),values)
to get this result
Pascal_Gallais_3-1719319995358.png

-4- duration_seconds

This formula to get total duration in seconds

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

 

See Solution in Thread

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

Pascal_Gallais_0-1719319688612.png

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
 
Pascal_Gallais_1-1719319846035.png

-2- Minute_value

IF(Separator_Pos>0,LEFT(values,Separator_Pos-1))
to get this result
Pascal_Gallais_2-1719319916138.png

-3- Second_value

IF(Separator_Pos>0,RIGHT(values,LEN(values)-Separator_Pos),values)
to get this result
Pascal_Gallais_3-1719319995358.png

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

Hi,

Sorry, I forgot this one in my previous answer.

Here is the formula:

 

SEARCH("/",values)
 
Regards,
Pascal

Thank you so much!!