Custom format (time) and wrapping text in a cell

[Phrank]

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

 

[T. Valko]

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

 

[RichardSchollar]

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

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

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

 

[Phrank]

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

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

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

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

 

[Lori]

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.

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

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

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

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

 

[Phrank]

Excellent Lori! Thank you so very very much!

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.

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

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

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

 

[Phrank]

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

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.

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

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

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