# The Cell Formula from HE *double hockey stick*



## dcanham (May 11, 2007)

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 (May 11, 2007)

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 (May 11, 2007)

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 (May 11, 2007)

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


----------



## Greg Truby (May 11, 2007)

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 (May 11, 2007)

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 (May 11, 2007)

> 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 (May 11, 2007)

> 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 (May 11, 2007)

> ...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 (May 15, 2007)

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.
.
.
.
.


----------



## dcanham (May 17, 2007)

Hah jm14...no I made him take me to Red Osher!


----------



## dcanham (May 17, 2007)

Well I am definately still in the 'noob' category.  I've programmed in C++ for a number of years, but there are some features of VB that work fundamentally different than C++ that keep hitting me in the face.  This new push at work to do everything through cell formulas to avoid the security warnings on macros has me stretching it.


----------



## Smitty (May 17, 2007)

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



None better to slap you upside the head and yank ya' back to reality though.  

I've had my share for sure, but like all good old cowboys, you don't get those stories unless you're buying. 

Smitty


----------

