Forcing Carriage Return in Custom Date Format

I just finished four days of DataPig workshops with Jon Peltier and Dick Kusleika. As always, everyone who attended had a blast and learned a ton of new tips and tricks. If you’ve never been to one, you’re seriously missing out.

.

Here’s an example of why I love these events.

Jon Peltier showed us a new trick that allows you to apply a carriage return in a custom date format. I have no idea how he figured this out, but it is damned cool.

.

Check out this screenshot. Notice how the dates are formatted so that the Month is over the Year. Now look at the formula bar. The values are actually dates.

.

Here’s how you do it:

First, enter a series of dates.

.

Next, highlight the dates and activate the Format Cells dialog box. There, you will click on Custom.

.

In the Type input box, enter

mmmm then press Ctrl+J then enter yyyy.

.

At this point, the dialog box doesn’t look all that special. But you will notice that the yyyy portion of your custom format is hidden.

.

When you press OK, you’ll get this formatting.

.

Now all you have to do is Wrap Text.

.

There you go – another trick I learned from Jon Peltier at the Excel Dashboards Bootcamp.

.

P.S. – We have only one more live workshop this year in September.

.

Well, I’m off to a relaxing three-day weekend of grilled meat and carbs.

20 thoughts on “Forcing Carriage Return in Custom Date Format

  1. Dave

    If you try to shrink the columnwidth, you’ll notice you’ll see ####’s way before you (or I) think you should.

    But a slight change can fix this.

    On the alignment tab of the format cell dialog, turn off wraptext (if it’s on). Turn on “shrink to fit”, then turn on wraptext.

    ps. You’ll have to adjust the rowheight manually if you autofit the rowheight.

  2. Rick Rothstein (MVP - Excel)

    An alternative to using CTRL+J is to hold down the ALT key and type the 0010 on the Number Key Pad (you cannot get this to work if you type the number from the main keyboard… only the Number Key Pad). In case you don’t recognize it, 10 is the ASCII code for the Line Feed character (the number you type in must be 4-digits long and zero front filled if the number is shorter than 4 digits). You can use this entry method to enter any ASCII character including those with no direct keyboard keyboard equivalents.

  3. Pedro Wave

    It’s good to know the years, months, days, hours, minutes and seconds elapsed from birth with this formula:
    =NOW()-DATE(1957,7,17)-31

    This custom cell format does the work:
    yy “years”
    mm “months”
    dd “days”
    hh “hours”
    mm “minutes”
    ss “seconds”

    Press Ctrl+J for each new Line Feed.

  4. Leonid

    I tried to format category axis labels on a chart using this technique. I wanted to format just chart labels and keep the source cells in the original format. It didn’t work. Dialog just didn’t take this custom format.
    The work around was to format source range, check “Linked to Source” check box in the format Axis dialog and then uncheck it. At this point chart axis labels got formatted as desired and the source cells can be restored to the original format not affecting chart labels.

  5. neil clarke

    Hi,

    this is exactly what i want to achieve for a chart X axes however after i hit enter the cell with the date just displays ” january(carriage return symbol)2012 “. in other words it seems to accept the carriage return as text rather than acting on it.

    Wrap text just shows the january.

    Have you gents any ideas where im going wrong?

    Many thanks!
    Neil

  6. Rick Rothstein (MVP - Excel)

    >> Wrap text just shows the january

    @Neil, try making the height for the row the cell is on taller.. the wrapped text should be there, but I think it is below the cell’s bottom border.

  7. Leana

    I have been searching for this feature for a very long time. This worked like a charm, including the instructions for getting rid of the ####### issue when you shrink the column width. Thank you, thank you, thank you so much!!! You have saved me so much time with my spreadsheets.

  8. Dragos

    Here’s another tip to complement the solution.

    I realized that the Ctrl+J trick didn’t work when trying to define custom formats on chart axes. But I was able to define the custom format while working on a table cell, then edit the chart axis and choose the format which I had defined before.

  9. Bill

    Excellent tip! And thanks to Dave for the shrink to fit tip. I was having trouble with the ####’s as well and found I had to widen the column to get rid of them. The idea was to stack the date in the first place to keep it narrow.

  10. Pajfa

    Hi, has anyone solved problem of squeezing the new date format where name of the day is stacked on top into a narrow column? I was disappointed to see I needed actually slightly more column width than before the Ctrl+J trick and Wrap text.

  11. Dave Marriott

    I know this is an old post but I wanted to know whether I could apply this change via vba. So I recorded it. When you see the code it seems so obvious as to whats going on. If the format code window was resizable I’m sure I would have worked this out years ago!

    Selection.NumberFormat = “dddd” & Chr(10) & ” mmm yy”

  12. Ben

    This is a great technique. I got it working for the X-axis labels of a category plot using the “link to source” technique described by Leonid.

  13. Lisa

    I tried adding the line break in with CTRL J to get the “bps” and that worked fine … but only if I typed a value into the cell with that format. If I tried to format a calculation, all I’d see was ” bps%” instead of the value. But if I typed in, say, .3%, I’d see “30 bps” and the % would be hidden below the part of the row that was showing.

    Why would it work on a static value but not a cell reference or formula?

Leave a Reply

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