If you need the result as a number, try entering:

=A2-A1

and then format cells with a custom number format: d "days" h "hours".

You can press ctrl+j in the format cells dialog box to place a line
break between the s and the quote.

If there are more than 31 days between start and end date you could try
instead:

=100*INT(A2-A1)+24*MOD(A2-A1,1)

with number format: 0 "days" 00 "hours" and press ctrl+j to put a line
break after days and before the quote.

Phrank wrote:

Thanks Biff and Richard,

Yes, I need the end result in further calculations, so the text route
won't work. Thank you for your input anyway, I appreciate that.

Frank

On 5 Dec 2006 01:03:53 -0800, "RichardSchollar"
wrote:

Frank

You can do it all within the TEXT function (altho Biff's comments re
text still stand (obviously)) - maybe you should hide the column
containing the numeric and place the text formula in the immediately
adjacent column? That way you wouldn't impact any existing formulas:

=TEXT(B1-A1,"d \d\a\y\s" & CHAR(10) & "h \h\o\u\r\s")

and remember to format the cell for wrapped text.

Richard



T. Valko wrote:

Maybe this:

=INT(B1-A1)&" days"&CHAR(10)&TEXT(MOD(B1,1)-MOD(A1,1),"h")&" hours"

Format to wrap text

That results in a TEXT string so it may "complicate" matters if you need to
use the result in other math calculations.

Biff

"Phrank" wrote in message
...
Hello,

I've got a time lapse formula going where I have start date/time in
column A, end date/time in column B, and elapsed time in column C. I
also have column C set with a custom formatting of
[d "days" h "hours"], which gives me exactly what I want. For
example,

Start date/time = 12/2/2006 8:00:00 AM
End date/time = 12/04/06 10:05 PM
Elapsed time = 2 days 14 hours

As I said, this works just fine. What I would like, though, is a more
narrow column and for the text to wrap, like below

2 days
14 hours

However, it will not wrap text (I get the ######) . I've tried
setting the column width an row height to autofit, and it still will
not wrap. Any ideas? Thanks.

Frank