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
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.
=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.