Page 1 of 1
format HH:mm:ss
Posted: Sun Jun 19, 2022 1:44 pm
by sna
Hi there!
hope you're all doing great.
I come here again for seeking a help to convert text string in Days Hours Mins into HH:mm:ss format.
I attach a template
Thanks
Re: format HH:mm:ss
Posted: Sun Jun 19, 2022 6:26 pm
by snasui
Try this formula.
B2
=(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",50)),1,50))*VLOOKUP(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",50)),51,50)),{"Days",1440;"Week",10080;"Hours",60;"Mins",1},2,0)+TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",50)),101,50))*VLOOKUP(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",50)),151,50)),{"Days",1440;"Week",10080;"Hours",60;"Mins",1},2,0))/60/24
Enter and copy down and custom format cells with
[h]:mm:ss
.
Re: format HH:mm:ss
Posted: Mon Jun 20, 2022 3:04 pm
by sna
Thank you so much,I will take a look