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

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

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

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

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

Excellent Lori! Thank you so very very much!

Frank

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

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

Hi again Lori,

I put this to the test at work, and overall it did work. However,
with this custom format, the row doesn't 'autofit', meaning it doesn't
grow in height in response to the split line. And if I increase the
height manually and then try to reduce the width of the column (which
is what this is all about, in order to save space), I get the #######
until I increase the width back to the same size as it takes to put
the contents on one line. Why is that?

Frank


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

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