The Cell Formula from HE *double hockey stick*

[dcanham]

Ok, I thought I would turn this debacle of mine into maybe a fun discussion. Here was the challenge, a friend of mine wanted to turn a 'total seconds' numerical value into a text string that broke the seconds down into day/hour/minute/second evaluation. The macro would have been a 10 line paradigm of simplicity.....However, he didn't want a macro because of the security warning that people then got with it. He couldn't wrap his mind around a way to do it as a cell formula. He challenged me to do it for a steak dinner. Well, I did it.....but the result is rather embarassing. I used a brute force method with '(U6*K7)' being the total seconds. This post has two functions. First, could someone embarass me more by coming up with a simplier way of doing it. Second, I would love to hear some of your funny stories about stupid things you did (like the formula below) that you never wanted to show anyone.....but worked.

=IF(K7>0,IF(INT((U6*K7)/86400)>0,INT((U6*K7)/86400)&" Days, ","")&IF(INT(((U6*K7)-(INT((U6*K7)/86400)*86400))/3600)>0,INT(((U6*K7)-(INT((U6*K7)/86400)*86400))/3600)&" Hours, ","")&IF(INT(((U6*K7)-(INT((U6*K7)/86400)*86400)-(INT(((U6*K7)-(INT((U6*K7)/86400)*86400))/3600)*3600))/60)>0,INT(((U6*K7)-(INT((U6*K7)/86400)*86400)-(INT(((U6*K7)-(INT((U6*K7)/86400)*86400))/3600)*3600))/60)&" Minutes, ","")&IF(((U6*K7)-(INT((U6*K7)/86400)*86400)-(INT(((U6*K7)-(INT((U6*K7)/86400)*86400))/3600)*3600)-((INT(((U6*K7)-(INT((U6*K7)/86400)*86400)-(INT(((U6*K7)-(INT((U6*K7)/86400)*86400))/3600)*3600))/60))*60))>0,((U6*K7)-(INT((U6*K7)/86400)*86400)-(INT(((U6*K7)-(INT((U6*K7)/86400)*86400))/3600)*3600)-((INT(((U6*K7)-(INT((U6*K7)/86400)*86400)-(INT(((U6*K7)-(INT((U6*K7)/86400)*86400))/3600)*3600))/60))*60))&" Seconds",""),0)

 

[Oaktree]

How about this:

=INT(U6*K7/86400)&" days, "&INT(MOD(U6*K7,86400)/3600) &" hours, "&INT(MOD(U6*K7,3600)/60)&" minutes, "&INT(MOD(U6*K7,60))&" seconds"

 

[Greg Truby]

This seems to work (you'll notice I'm way lazier than Matt :wink: )

=TEXT(INT(TotSeconds/86400), "0 ""days """) & TEXT(TotSeconds/86400, "h ""hours"" m ""minutes"" s ""seconds""")

Note: TotSeconds is a named formula: =Sheet1!$K$7*Sheet1!$U$6

 

[dcanham]

well, see now all of you are embarassing me .....gimme some stories of your own. :wink:

 

[Greg Truby]

Don't feel too bad. :wink: Pretty much any time I try my hand at a thread that calls for some complex array formula, I mutter a prayer that Aladin, Barry or Dom ain't gonna post right behind me with something that makes me look like an addle-pated greenhorn.

 

[Oaktree]

Somewhere in the bowels of the board is a post of mine where I used a combination of ROW and CHAR and copying and probably filtering and yadda yadda yadda only to have HOTPEPPER, I think it was, come along and say "...Or you could just use the CODE function".

On the bright side, I now know what that one's for

 

[Joe4]

He challenged me to do it for a steak dinner. Well, I did it.....

I see that you are in Rochester. Did you make him take you to the Peter Geyer Steakhouse?

 

[Lewiy]

Don't feel too bad. Pretty much any time I try my hand at a thread that calls for some complex array formula, I mutter a prayer that Aladin, Barry or Dom ain't gonna post right behind me with something that makes me look like an addle-pated greenhorn.

I concur! But it's all a learning process I guess!!

 

[Greg Truby]

...But it's all a learning process I guess!!
~Lewiy

Yeah, kain't say that I cotton much ta the taste o' humble pie. But, like some veggies I could name, I reckon it's good fer a cowpoke ta get fed a bit every now an' again. So's whenever Aladin & his disciples serve me up a nice king-sized slice, I try ta be polite 'n' thank 'em.

 

[steve case]

So you want to know what kinds of stupid things a newbie to this stuff like me has done.

Before I knew about Paste special Transpose, I did it the hard way.

I also did a Vlookup function the hard way with a macro.

I've redone those "toys" to avoid embarrassment if someone who knows anything ever looked at them carefully.
.
.
.
.