Excel – Custom Date/Time format – Wrap to 2 lines

[DaleWatson123321]

Does anyone have a “formatting” solution to getting the Date to show on the first line and the Time on the second line (in the same cell) ?

Here’s what I’ve done...

First, checking off “Wrap text” under Alignment does not work (no surprise as the date is a number).

With the following formula, I get the proper results, but this means having to place the date/time in a separate cell and apply the formula and/or use VBA to convert the date/time to text.
=TEXT(M3,"mm/dd/yy")&CHAR(10)&LOWER(TEXT(M3,"h:mm AM/PM"))

My preference, would be to “get Excel to do the work” (automatically) – by using a “format” – thus my original question – Does anyone have a “formatting” solution – perhaps a special character for use in a custom format ?

Using the following “custom” format, I’ve been able to get Excel to split the date/time onto 2 lines – but it doesn’t work properly… “mm/dd/yy”, followed by <Alt> 010 (which produces a hard return), followed by “h:mm AM/PM”.

With Ariel font at 8 point, the date by itself displays and prints properly with a cell width of 6. Therefore, using the above format, it “should” be possible to get the date/time to display and print properly in a column width of 6.

Here’s what happens… The date/time will only display (properly) at a width of 11.43 (but creating a lot of wasted space). With a width < 11.43, Excel displays the ##### characters.

Even at the width of 11.43, Excel still causes a problem by printing the ##### characters (even though the date/time displays properly). To get Excel to print the date/time (with the extra wasted space), the cell width has to be increased to 13.4.

I hope someone has come up with a “formatting” solution, and will share it with the rest of us. It’ll be much appreciated.

Regards, ...Dale Watson dalwatson@gov.mb.ca

 

[Zathras]

Not sure I understand the problem. It seems to work just fine if WrapText is checked and this formula is used:

=TEXT(M3,"mm/dd/yy         hh:mm:ss am/pm")

The extra spaces allow for the width to vary but still wrap the way you want.

 

[DaleWatson123321]

Zathras,

Thanks for responding. I can appreciate that my posting is "rather lengthy", but if you re-read it, you'll notice I had included a "formula" solution.

I'm not seeking a "formula" solution, but rather a customer "formatting" solution.

Because I managed to made "some progress" with the formatting option, I thought perhaps someone out there would have discovered a custom "format" solution that works properly - i.e. without any of the spacing/printing problems I described.

If there's a format solution out there, great. If not, or perhaps in any event, M$ should include this custom format "extra spacing irregularity" (bug?) on their "to do list" for their next release. ;-)

Regards, ...Dale Watson dalwatson@gov.mb.ca

 

[Zathras]

You can type [Alt]+10 in the middle of the custom format to cause a line break:

mm/dd/yy [] h:mm:ss AM/PM

Type [Alt]+10 where you see the [] and format the cell for "Wrap Text"

 

[macropod]

Hi Dale,

From my experience, forcing a line wrap via a cutom format doesn't have much effect on the cell width Excel requires to display the underlying value. It seems Excel isn't intelligent enough to work out that it only needs the longest line length.

Cheers

 

[DaleWatson123321]

Zathras,

Thanks again, but...

My initial posting included...
"Using the following “custom” format, I’ve been able to get Excel to split the date/time onto 2 lines – but it doesn’t work properly… “mm/dd/yy”, followed by <Alt> 010 (which produces a hard return), followed by “h:mm AM/PM”."

Your recent posting includes...
"You can type [Alt]+10 in the middle of the custom format to cause a line break:"

Our "solutions" are effectively the same - with the result being as I described in my initial posting (please re-read) - where I describe a significant problem with the spacing. The net result is that it seems impossible to get the cell width to 6 (to accommodate the longest length of one line). Anything less than a width of 13.4 will cause Excel to print the ##### characters, and 13.4 will include a lot of wasted space.

Macropod,

Your assessment seems to match mine...
"It seems Excel isn't intelligent enough to work out that it only needs the longest line length."

While this is a fairly minor problem (bug?), hopefully Microsoft will get around to fixing such "annoyances". Afterall, isn't "intelligence" a very significant part of the computer industry, and shouldn't Microsoft want to continue to be seen as a big player ?

Regards, ...Dale Watson dalwatson@gov.mb.ca