Hi, Wi have some cells with below mentioned time values, I could not figure out how i should covert them and sum it up. Would appreciate some help.
Cell1: 15d 20h 2m 25s
Cell2: 7d 14h 42m 13s
Cell3: 13d 13h 16m 10s
I would like to have this as a result on Cell4: 888:00:48
Thank you.
Are any of the times less than 0 days?
If so what is the format for those values, ie does it say 0d or does it just omit that value?
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
Are the cells formatted as text or as time?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
It can be also less then 0days.
Then it is like this,
20h 10m 20s
or
15m 35s
or
55s
Cells are formatted as General.
Thank you,
I had hoped that one of the parsing gurus might have picked up on this. They haven't. So, this Brute Force and Ignorance formula, which DOES work is your starting point. Over to others to make a nicer one!!
Formula:
Please Login or Register to view this content.
Thank you it worked exactly how i wanted. Although i made it even longer. It is extreme. :D
=(IFERROR(LEFT((INDIRECT(A2&"!B3")),FIND("d",(INDIRECT(A2&"!B3")))-1)+0,0)+IFERROR((LEFT(SUBSTITUTE((INDIRECT(A2&"!B3")),LEFT((INDIRECT(A2&"!B3")),FIND("d",(INDIRECT(A2&"!B3")))),""),FIND("h",SUBSTITUTE((INDIRECT(A2&"!B3")),LEFT((INDIRECT(A2&"!B3")),FIND("d",(INDIRECT(A2&"!B3")))),""))-1)+0)/24,0)+IFERROR(LOOKUP(10^10,RIGHT(LEFT(TRIM(MID((INDIRECT(A2&"!B3")),1,LEN((INDIRECT(A2&"!B3")))-3)),LEN(TRIM(MID((INDIRECT(A2&"!B3")),1,LEN((INDIRECT(A2&"!B3")))-3)))-1),ROW(INDIRECT("1:"&LEN(LEFT(TRIM(MID((INDIRECT(A2&"!B3")),1,LEN((INDIRECT(A2&"!B3")))-3)),LEN(TRIM(MID((INDIRECT(A2&"!B3")),1,LEN((INDIRECT(A2&"!B3")))-3)))-1)))))+0)/1440,0)+LOOKUP(10^10,RIGHT(LEFT((INDIRECT(A2&"!B3")),LEN((INDIRECT(A2&"!B3")))-1),ROW(INDIRECT("1:"&LEN(LEFT((INDIRECT(A2&"!B3")),LEN((INDIRECT(A2&"!B3")))-1)))))+0)/86400)+(IFERROR(LEFT((INDIRECT(A2&"!E3")),FIND("d",(INDIRECT(A2&"!E3")))-1)+0,0)+IFERROR((LEFT(SUBSTITUTE((INDIRECT(A2&"!E3")),LEFT((INDIRECT(A2&"!E3")),FIND("d",(INDIRECT(A2&"!E3")))),""),FIND("h",SUBSTITUTE((INDIRECT(A2&"!E3")),LEFT((INDIRECT(A2&"!E3")),FIND("d",(INDIRECT(A2&"!E3")))),""))-1)+0)/24,0)+IFERROR(LOOKUP(10^10,RIGHT(LEFT(TRIM(MID((INDIRECT(A2&"!E3")),1,LEN((INDIRECT(A2&"!E3")))-3)),LEN(TRIM(MID((INDIRECT(A2&"!E3")),1,LEN((INDIRECT(A2&"!E3")))-3)))-1),ROW(INDIRECT("1:"&LEN(LEFT(TRIM(MID((INDIRECT(A2&"!E3")),1,LEN((INDIRECT(A2&"!E3")))-3)),LEN(TRIM(MID((INDIRECT(A2&"!E3")),1,LEN((INDIRECT(A2&"!E3")))-3)))-1)))))+0)/1440,0)+LOOKUP(10^10,RIGHT(LEFT((INDIRECT(A2&"!E3")),LEN((INDIRECT(A2&"!E3")))-1),ROW(INDIRECT("1:"&LEN(LEFT((INDIRECT(A2&"!E3")),LEN((INDIRECT(A2&"!E3")))-1)))))+0)/86400)+(IFERROR(LEFT((INDIRECT(A2&"!H3")),FIND("d",(INDIRECT(A2&"!H3")))-1)+0,0)+IFERROR((LEFT(SUBSTITUTE((INDIRECT(A2&"!H3")),LEFT((INDIRECT(A2&"!H3")),FIND("d",(INDIRECT(A2&"!H3")))),""),FIND("h",SUBSTITUTE((INDIRECT(A2&"!H3")),LEFT((INDIRECT(A2&"!H3")),FIND("d",(INDIRECT(A2&"!H3")))),""))-1)+0)/24,0)+IFERROR(LOOKUP(10^10,RIGHT(LEFT(TRIM(MID((INDIRECT(A2&"!H3")),1,LEN((INDIRECT(A2&"!H3")))-3)),LEN(TRIM(MID((INDIRECT(A2&"!H3")),1,LEN((INDIRECT(A2&"!H3")))-3)))-1),ROW(INDIRECT("1:"&LEN(LEFT(TRIM(MID((INDIRECT(A2&"!H3")),1,LEN((INDIRECT(A2&"!H3")))-3)),LEN(TRIM(MID((INDIRECT(A2&"!H3")),1,LEN((INDIRECT(A2&"!H3")))-3)))-1)))))+0)/1440,0)+LOOKUP(10^10,RIGHT(LEFT((INDIRECT(A2&"!H3")),LEN((INDIRECT(A2&"!H3")))-1),ROW(INDIRECT("1:"&LEN(LEFT((INDIRECT(A2&"!H3")),LEN((INDIRECT(A2&"!H3")))-1)))))+0)/86400)
There will be a much easier way. It's just not occurring to me, though!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks