Ask Your Question
0

Date on two lines

asked 2019-11-06 09:57:34 +0100

Chris Vella gravatar image

Is it possible to insert a line break inside a user-defined custom format to show dates on two lines as follows?

Sunday
1 January 2019
edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2019-11-06 10:19:51 +0100

keme gravatar image

updated 2019-11-06 10:37:58 +0100

I don't think you can achieve this with a cell format.

Workaround formulas (use one of these):

=TEXT(TODAY();"DDDD")&CHAR(10)&TEXT(TODAY();"D MMMM YYYY")
=TEXT(TODAY();"DDDD"&CHAR(10)&"D MMMM YYYY")

In both formulas, replace TODAY() with a reference to the real date you are working with, as applicable. Note two occurrences of TODAY() in the first formula.

Note that all date calculation/sorting/filtering should be done on the aforementioned "real date", which the workaround references. Thanks @Opaque for mentioning it (see comment).

Newline is inserted by control character number 10. CHAR(10) can be inserted into the format string for TEXT(), or into the final string expression, as in the formulas above. The "\n" escape sequence matches newline in some contexts, but not for cell format/styles. At least I haven't found any way to do it yet.

edit flag offensive delete link more

Comments

2

Drawback: It is no longer a real calc date, but a text.

Opaque gravatar imageOpaque ( 2019-11-06 10:28:27 +0100 )edit

Yes, even code like that doesn't help:

sub AddDateFormatWithNewline
    dim controller as object, oLocale as object
    controller = ThisComponent.CurrentController
    oLocale = controller.Selection.CharLocale

    dim nFmtId as Long, sFmtStr as String
    sFmtStr = "DDDD" & chr(10) & "D MMMM YYYY"
    nFmtId = ThisComponent.NumberFormats.queryKey(sFmtStr, oLocale, False)
    if nFmtId = -1 then
       nFmtId = ThisComponent.NumberFormats.addNew(sFmtStr, oLocale)
    end if

    controller.Selection.NumberFormat = nFmtId
end sub
Mike Kaganski gravatar imageMike Kaganski ( 2019-11-06 10:43:43 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-11-06 09:57:34 +0100

Seen: 25 times

Last updated: Nov 06