Google-sheets – How to add a line break in a custom date format in Google Spreadsheets

customizationdateformattinggoogle sheets

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?

Best Answer

This can be achieved with the built-in developer tools of the browser. They can be used to input any value as a custom number format, including text with newlines and tabs.

  1. Start adding a custom number format.
  2. Press F12 to open the developer tools.
  3. Select the console tab.
  4. Paste the following text into the console:
Object.defineProperty($("[label='Custom number format']"), "value", {value: "dd/mm/yyyy\n (dddd)"})
  1. Substitue "dd/mm/yyyy\n (dddd)" with your own format. Use \n in place of a newline.
  2. Press enter to execute the script.
  3. Apply the format. Even though the actual field contents haven't changed, our defined property will be read from when the form is submitted.

Notes

Although the original poster did ask for non-script solutions, that I believe was in reference to Google Apps Script. This answer may also be useful for others.

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.

The reason we are redefining the "value" property is because the type of field used doesn't support newlines, even when set in the console.

Related Topic