Duration TEXT to correct formating

[mikeeTHINK]

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

[Pascal_Gallais- · Accepted solution]

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

[mikeeTHINK]

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

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

[Pascal_Gallais-]

Hi,

Sorry, I forgot this one in my previous answer.

Here is the formula:

SEARCH("/",values)

Regards,

Pascal

[mikeeTHINK]

Thank you so much!!