14

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?

9
  • Is it Ok to provide a script solution already? Mar 18, 2015 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, 2015 at 15:59
  • May I also have a shot at creating that script? Mar 18, 2015 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, 2015 at 15:17
  • 4
    @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, 2015 at 2:34

3 Answers 3

5

Google Sheets will both receive a number format with multiple lines and display cells using it: the cell value 3/21/2022 being displayed as 2022-03-21 followed by Monday in parenthesis on a separate line of the same cell

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)"})
  1. Substitue "YYYY-MM-DD\n(dddd)" with your own format. Use \n in place of a newline.
  2. Press enter to execute the script.
  3. 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.

3
  • 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, 2020 at 7:52
  • While this is a neat trick, unfortunately, this no longer works as of 3/20/2022, likely due to changes in the Google Sheets HTML. I spent 10 minutes trying to find a new way to target the specified page component, but couldn't quickly identify one.
    – hberg
    Mar 20 at 19:55
  • @hberg Still works for me. It might be a slow rollout or maybe a different display language. Right-click > Inspect on the input field should show the HTML for it. What does your label say?
    – metarmask
    Mar 21 at 21:31
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, 2020 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. Feb 14, 2019 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.