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
:D 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