SOLVED

Convert multiple combinations of date text strings into a seconds

%3CLINGO-SUB%20id%3D%22lingo-sub-224293%22%20slang%3D%22en-US%22%3EConvert%20multiple%20combinations%20of%20date%20text%20strings%20into%20a%20seconds%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-224293%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone!%20Maybe%20someone%20can%20help%20me%20with%20this%20problem...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20using%20a%26nbsp%3Bsoftware%20to%20manage%20telemarketing%20campaigns.%20This%20software%20allows%20me%20to%20create%20several%20informs%20and%20one%20of%20them%20is%20to%20know%20of%20much%20time%20an%20operator%20is%20logged%20in%20a%20campaign.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20is%20that%20the%20output%20is%20in%20text%20format%20and%20with%20multiple%20combinations%20such%20as%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-%202d19h20m55s%3CBR%20%2F%3E-%204h58m56s%3C%2FP%3E%3CP%3E-%26nbsp%3B15h37m5s%3C%2FP%3E%3CP%3E-%2021m55s%3C%2FP%3E%3CP%3E-%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20goal%20is%20to%20create%20a%20function%20that%20can%20convert%20all%20of%20this%20possible%20combinations%26nbsp%3Bin%20seconds.%3C%2FP%3E%3CP%3EI%20started%20with%20this%26nbsp%3B%3D%2BLEFT(%24C4%3BFIND(%22d%22%3B%24C4%3B1)-1)*24*60*60%20but%20now%20I'm%20stocked.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20it%20that%20possible%20or%20I%20have%20to%20create%20a%20function%20for%20any%20possible%20combination%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-224293%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDate%20Calculations%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDate%20format%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-224574%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20multiple%20combinations%20of%20date%20text%20strings%20into%20a%20seconds%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-224574%22%20slang%3D%22en-US%22%3E%3CP%3EWow!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAmazing%20guys.%20Thank%20you%20so%20much%20for%20your%20answers%2C%20both%20of%20them%20valid.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20owe%20you%20one!%20%3B-)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-224349%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20multiple%20combinations%20of%20date%20text%20strings%20into%20a%20seconds%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-224349%22%20slang%3D%22en-US%22%3E%3CP%3EAnother%20way%20could%20be%20with%20using%20of%20EVALUATE%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet%20define%20in%20Name%20Manager%20new%20name%2C%20let%20say%20TransformToSeconds%2C%20with%20value%3C%2FP%3E%0A%3CPRE%3E%3DEVALUATE(%22%3D%22%26amp%3BSUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2%2C%22s%22%2C%22%22)%2C%22m%22%2C%22*60%2B%22)%2C%22h%22%2C%22*60*60%2B%22)%2C%22d%22%2C%22*24*60*60%2B%22))%3C%2FPRE%3E%0A%3CP%3ESUBSTITUTE%20here%20transforms%20text%20string%20into%20the%20text%20like%20(for%20the%20first%20value)%3C%2FP%3E%0A%3CPRE%3E2*24*60*60%2B19*60*60%2B20*60%2B55%3C%2FPRE%3E%0A%3CP%3Eand%20with%20EVALUATE%20we%20calculate%20that%20formula%3C%2FP%3E%0A%3CP%3EAfter%20that%20in%20next%20to%20the%26nbsp%3Bsource%20string%20we%20put%20the%20formula%3C%2FP%3E%0A%3CPRE%3E%3DTransformToSeconds%3C%2FPRE%3E%0A%3CP%3Ewhich%20returns%20the%20result.%3C%2FP%3E%0A%3CP%3EThe%20only%20point%20workbook%20with%20EVALUATE%20shall%20be%20saved%20as%20macro-enabled%20one%20(*.xlsm).%20Attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-224339%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20multiple%20combinations%20of%20date%20text%20strings%20into%20a%20seconds%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-224339%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Daniel%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20such%20a%20complex%20situation%2C%20the%20best%20approach%20is%20to%20create%20some%20helper%20columns%20to%20extract%20days%2C%20hours%2C%20minutes%2C%20and%20seconds%20from%20each%20string.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOnce%20we%20get%20each%20interval%20separated%20in%20a%20column%2C%20we%20can%20easily%20convert%20them%20all%20into%20seconds%20as%20below%20screenshot%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F38982iCFFA29E3DAE2815B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Calculate%20Seconds.png%22%20title%3D%22Calculate%20Seconds.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20find%20the%20attached%20file.%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi everyone! Maybe someone can help me with this problem...

 

I'm using a software to manage telemarketing campaigns. This software allows me to create several informs and one of them is to know of much time an operator is logged in a campaign.

 

The problem is that the output is in text format and with multiple combinations such as:

 

- 2d19h20m55s
- 4h58m56s

- 15h37m5s

- 21m55s

- etc.

 

My goal is to create a function that can convert all of this possible combinations in seconds.

I started with this =+LEFT($C4;FIND("d";$C4;1)-1)*24*60*60 but now I'm stocked.

 

Is it that possible or I have to create a function for any possible combination?

 

Thanks in advance.

3 Replies

Hi Daniel,

 

In such a complex situation, the best approach is to create some helper columns to extract days, hours, minutes, and seconds from each string.

 

Once we get each interval separated in a column, we can easily convert them all into seconds as below screenshot:

Calculate Seconds.png

 

Please find the attached file.

Regards

Best Response confirmed by Daniel Martínez León (New Contributor)
Solution

Another way could be with using of EVALUATE

 

Let define in Name Manager new name, let say TransformToSeconds, with value

=EVALUATE("="&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"s",""),"m","*60+"),"h","*60*60+"),"d","*24*60*60+"))

SUBSTITUTE here transforms text string into the text like (for the first value)

2*24*60*60+19*60*60+20*60+55

and with EVALUATE we calculate that formula

After that in next to the source string we put the formula

=TransformToSeconds

which returns the result.

The only point workbook with EVALUATE shall be saved as macro-enabled one (*.xlsm). Attached.

Wow!

 

Amazing guys. Thank you so much for your answers, both of them valid.

 

I owe you one! ;-)