12

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?

7
  • Is it Ok to provide a script solution already? – Jacob Jan Tuinstra Mar 18 '15 at 14:03
  • That wont' answer the question at all, sorry. I already have a script for that. (It's on my mind, but I have it!) – bruno Mar 18 '15 at 15:59
  • May I also have a shot at creating that script? – Jacob Jan Tuinstra Mar 18 '15 at 19:54
  • 1
    If the correct answer is "unfortunately, it's not possible to do that", then I would open a new question about the script. But I would like to see a good source backing up the not-possible assertion. – bruno Mar 19 '15 at 15:17
  • 3
    @JacobJanTuinstra You should feel free to post your script anytime, OP's remark "I already have a script" is besides the point, as the answers are not just for them. – user79865 Oct 25 '15 at 2:34
3

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.

1
  • that so cool! Initially it wasn't very clear what this entailed, but reading the instructions carefully I realised it is directly setting the Custom Format input field value using JavaScript within the browser console. Neat trick! – Aurovrata Sep 7 '20 at 7:52
2

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.

1
  • this answer works, but requires an additional row/col to make it work. @metarmask answer below is even better as it format the date cell directly. – Aurovrata Sep 7 '20 at 7:54
0

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.

1
  • 2
    Tried this on Mac using Ctrl and Command; both special characters are ignored. – Dan Mergens Feb 14 '19 at 21:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.