Rick Rothstein wrote:
Try this value in A1...

=1.01694444444444

I get your formula showing this...

1 hour, 1 minutes

Right, I missed an INT() around the last test. Converted to remove
the commas that weren't necessary:

=TRIM(IF(INT(A1/168),INT(A1/168)&
" week"&IF(INT(A1/168)1,"s "," "),"")&
IF(INT(MOD(A1,168)/24),INT(MOD(A1,168)/24)&
" day"&IF(INT(MOD(A1,168)/24)1,"s "," "),"")&
IF(INT(MOD(A1,24)),INT(MOD(A1,24))&
" hour"&IF(INT(MOD(A1,24))1,"s "," "),"")&
IF(MOD(A1,1),INT(MOD(A1,1)*60)&
" minute"&IF(INT(MOD(A1,1)*60)1,"s "," "),""))


which is a little shorter than your version, which I like.

While my code is longer than yours character-wise (SUBSTITUTE is such a
long function name<g), it uses less than half as many individual
function calls. I could have made my code shorter yet, except that I
kept bumping into Excel's limit on the amount of nested functions calls
it allows.



How about this:

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
" "&INT(A1/168)&" weeks "&INT(MOD(A1,168)/24)&" days ",
" 0 weeks","")," 1 weeks","1 week")," 0 days","")," 1 days"," 1 day")&
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
TEXT(MOD(A1,24)/24," h"" hours "" m"" minutes"""),
" 0 minutes","")," 1 minutes","1 minute")," 0 hours","")," 1 hours","1 hour"))


Also fixes a problem with multiples of 10 weeks.