7

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?

  • 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
  • 2
    @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
1

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.

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.

  • 2
    Tried this on Mac using Ctrl and Command; both special characters are ignored. – Dan Mergens Feb 14 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.