Date Form Does Not Wrap

E

egun

Simple Problem:

I have formatted a couple of cells with the "4/23/2007 10:31:39 AM" style
of format, and set the cells to Wrap Text. I want to change the column width
so that the time portion is on the second line and the date on the first.
When I narrow the column, the text doesn't wrap. Instead I get the
"#########" that says the column is too narrow.

How do I get the text to wrap?

Thanks,

Eric
 
J

Joel

You may need to put the time in two different row. format first row the date
and the second row for time. You can put a border around both cells so it
looks like it is one cell.
 
D

Dave Peterson

Not so simple answer.

You can get the text to wrap, but you can't get the ######'s to go away if excel
thinks the column is too skinny.

And excel will base the columnwidth on the width before wrapping.

If you want this, then select your range
format|cells|number tab|Custom category
mm/dd/yyyy_hh:mm:ss AM/PM
But don't use the _ character--use ctrl-j

Then change the alignment tab's wrap text to true.

You'll notice that you may have to fit the rowheight automatically, too.
 
B

bj

use a helper cell with the actual date and use
=TEXT(H8,"m/d/yyyy")&" "&TEXT(H8,"h:m:s AM/PM")
change the time and date format to the particulars you want.
 
E

egun

That didn't work for me because I still get the "######" when I shrink the
column. Since the objective is to shrink the column width, this won't help.

However, I found a wierd workaround. First I set the cells to "Shrink to
Fit". Then, with that still selected, I also set the "Wrap Text" to on. The
"Shrink to Fit" is grayed out, but stays checked. I then shrank the column,
and guess what - the text wrapped just fine!! How's that for unpredictable
behavior.

Thanks for your inputs,

Eric
 
D

Dave Peterson

Or
=TEXT(H8,"m/d/yyyy")&char(10)&TEXT(H8,"h:m:s AM/PM")

=Char(10)
is the same as the alt-enter.
 
D

Dave Peterson

Thanks for posting back. I hadn't noticed that.
That didn't work for me because I still get the "######" when I shrink the
column. Since the objective is to shrink the column width, this won't help.

However, I found a wierd workaround. First I set the cells to "Shrink to
Fit". Then, with that still selected, I also set the "Wrap Text" to on. The
"Shrink to Fit" is grayed out, but stays checked. I then shrank the column,
and guess what - the text wrapped just fine!! How's that for unpredictable
behavior.

Thanks for your inputs,

Eric
Click to expand...
 
Top