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 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.
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.
3 Replies
Wow!
Amazing guys. Thank you so much for your answers, both of them valid.
I owe you one! ;-)
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
