How to add a line break in a custom date format in Google Spreadsheets?

Question

Google Spreadsheets give us a tool to create and use a customized data format for date values.

While the tool allows us to use arbitrary characters, like slashes and commas between the date fields (day, month, day of week...), I fail to see how to add a line break to my custom date format, what would allow me see dates like this:

18/03/2015
 (Tuesday)

Can this format be achieved with the existing tool? Is there a new-line character? Or will I have to use custom functions for that?

Answer 1

You can insert the vertical tab unicode character U+000b

You can copy and paste it from between these brackets: ][

Answer 2

Google Sheets will both receive a number format with multiple lines and display cells using it:

The problem is that the "Custom number format" input field does not support line breaks/newlines. Luckily we can trick the page into sending these when we Apply:

1. Start adding a custom number format.
2. Press F12 to open the built-in developer tools of the browser.
3. Go to the console tab.
4. Paste the following code into the console:

Object.defineProperty($("[label='Custom number format']"), "value", {value: "YYYY-MM-DD\n(dddd)"})

5. Substitue "YYYY-MM-DD\n(dddd)" with your own format. Use \n in place of a newline.
6. Press enter to execute the script.
7. Apply what appears to be the old format.

Technical notes

The reason we are selecting based on the label instead of right-clicking the field and using $0 is because it is already defined by the page. It's also unclear if the ID will change, even though an ID would mean the answer could apply to all languages without changes.

Assigning to value of a text input always erases newlines which is why we have to shadow the built-in property.

Answer 3

You can do it in a fairly ugly way by doing the full date formatting without the line break and then parse it out and add the line break in the formula. CHAR(10) doesn’t seem to work in the custom formatting nor does \n.

Assuming your date is in cell A1 and set to read Saturday, October 31, 2015 (which is one of the defaults)

You can break it down and recombine it as follows-

• Cell B1 =find(",",A1) gets you the first comma.
• Cell C1 =len(A1) gets you the total length of the date.
• Cell D1 =left(A1,B1-1) lets you grab just the day.
• Cell E1 =right(A1,C1-B1-1) grabs everything except the day.
• Cell E1 =E1&CHAR(10)&"("&D1&")" takes the chopped up pieces and recombines them while adding the parenthesis.

There are a couple of ways you could mash this into fewer cells/steps but I figured it was easier to see broken down like this.

Option 2- Set your custom number format up like " "mmmm" "d", "yyyy" "(""dddd")"

Assuming you have that in cell A5, you can break it down as follows.

• Cell B5 =find("(",A5) finds the first parenthesis. Cell C5
• =len(A5) gets the total length of the date Cell D5
• =(left(A5,(B5-2))&char(10)&right(A5,C5-B5+1)) repositions the
  pieces as desired and adds the line break.

Two formula examples are here.

Answer 4

If Google Spreadsheets is anything like Excel (it is compatible, but I don't know how far this goes), then you can use ctrl+J to enter a new line character in custom number formats.