Forum Discussion
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...
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.
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:
Please find the attached file.
Regards