Grrrrr,

I forgot to test for zero minutes, try this instead

=IF(INT(INT(A1)/168)0,INT(INT(A1)/168)&" weeks
","")&IF(MOD(INT(A1),168)=24,INT(MOD(INT(A1), 168)/24)&" Days
","")&IF(AND(MOD(INT(A1),24)<24,MOD(INT(A1),24)<0 ),MOD(INT(A1),24)&" Hours
","")&IF(MOD(A1,1)0,RIGHT(TEXT(A1/24,"h:mm"),2)&" mins","")

Mike

"Mike H" wrote:

Hi,

My head is now aching. This assumes your formula is in a1

=IF(INT(INT(A1)/168)0,INT(INT(A1)/168)&" weeks
","")&IF(MOD(INT(A1),168)=24,INT(MOD(INT(A1), 168)/24)&" Days
","")&IF(AND(MOD(INT(A1),24)<24,MOD(INT(A1),24)<0 ),MOD(INT(A1),24)&" Hours
","")&RIGHT(TEXT(A1/24,"h:mm"),2)&" mins"

I'm afraid the for 1 week or day it still uses the Plural of weeks or days.
We'll see if someone cracks that

Mike

Mike

"Lady_Aleena" wrote:

Dear Bernard;

This is not time sensitive, no pun intended, so whatever time you can spare
to help me will be appreciated. I have been using Excel for years and know
that I have not even scratched the surface on what it can do. Even if you
just help thaw me out just a little with a nudge (the beginnings of it) would
be appreciated.

LA

"Bernard Liengme" wrote:

My Lady:
I expect someone could write a single, very long Excel formula to do this
but I am not going to try.
If you would be happy with a UDF (user defined function) in VBA please let
me know and I may have time to try it (if my sovereign lady give me time off
this weekend)

To learn more able VBA visit;

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Your obedient servant
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme



"Lady_Aleena" wrote in message
...
I have been going around in circles trying to write a formula to get weeks,
days, hours, and minutes.

(e38/h37)/2 is the amount of hours.

The format I would like the output to have is:

X week(s), X day(s), X hour(s), X minute(s)

If one doesn't apply, I would like it to NOT be displayed.

This should be easy, but with all of the rounddowns, mods, etc. plus
having
all the text added into all of the if statements to get the plurals right,
I
am just frozen.