Custom Date Format Wrapped To Two Lines- Excel Training

Custom Date Format Wrapped To Two Lines- Excel Training
Here’s a pretty cool trick that you may not have seen before in Excel.

Custom date format wrapped to two lines

The date entered in the image above doesn’t look so unusual.

You could simply type ‘Saturday’ press ALT+ENTER, to go to the next line, and type ‘December 25, 2010’

What may surprise you though, is that the value in this cell is actually a real date, such as 25/12/2010 (or 12/25/2010 in the US).

Custom date format wrapped to two lines

Let me show you a simple little trick to force a custom date format to wrap to two lines…and you’re not limited only to the format I’ve used in this example.

1) Enter December 25, 2010 in an empty cell;

2) Select the cell;

3) On the Home tab, click small arrow on bottom right corner of the Number group;

4) In the Category section, select Custom;

5) In the Type field, enter dddd. You will see Saturday in the Sample area of the Format Cells dialog box;

6) Still in the Type field, hold down the ALT key and type 0010 using the numeric keypad. ALT+0010 is the code for the line feed character. After you type (ALT+) 0010, it may appear that the previous code you just entered disappeared. It didn’t. Now type mmmm dd, yyyy;

Just to review, in the Type field you should have entered dddd ALT+0010 mmmm dd, yyyy ;

7) Click the Alignment tab (in the Format Cells dialog) and select the Wrap Text option;

8) Click OK;

9) Finally, increase the row height to display the two-line date. If you don’t want to change the row heights in your worksheet, you can select the cell containing the date and the cell immediately below and merge the cells (Home tab, Alignment group, Merge & Center, Merge Cells).

Here are some other variations you could use for your two-line dates.

Custom date format wrapped to two lines

6 thoughts on “Custom Date Format Wrapped To Two Lines- Excel Training

  1. So this is great and I am happy to know about it but Excel still requires the full column width to display the date, i.e. I can’t reduce the column width now that my date is on 2 lines or I get #######. 🙁

  2. This is the same for me, I get “#######”. And yes, I was so happy to learn about this too. But t the end, it does not allow to save space on display. Have you figured out how to work around this issue ? Thx.

  3. really great tip – but I have to agree with Elizabeth – disappointing that can’t reduce column width which was purpose of looking up this tip. I abbrev. the day and date to: ddd alt+0010 dd mmm yy to reduce col. width as much as possible.

  4. This width problem is a “feature” in Excel. You can get what you want by using a formula to copy the date which then allows the width of the cell to be reduced. For example:
    =TEXT(D8,”dddd”)&CHAR(10)&TEXT(D8,”dd mmm yyyy”)&CHAR(10)&TEXT(D8,”hh:mm AM/PM”) giving
    24 Jul 2016
    06:41 AM

  5. It seems that you can get around the ######## problem by ticking ‘Shrink to fit’.
    (If it’s greyed out just untick ‘Wrap text’, tick ‘Shrink to fit’ then re-tick ‘Wrap text’). Unexpectedly, with ‘Shrink to fit’ ticked, the date’s font size doesn’t shrink (probably because ‘Wrap text’ is on), which is what I want.

Leave a Reply

Your email address will not be published. Required fields are marked *