Forum Discussion

Daniel Martínez León's avatar
Daniel Martínez León
Copper Contributor
Aug 04, 2018
Solved

Convert multiple combinations of date text strings into a seconds

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...
Date Calculations
Date format
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Aug 04, 2018

    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.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Aug 04, 2018

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.

Resources