User-defined date formatting not printing as displayed in spreadsheet

usrrr · February 2, 2024, 2:57am

I’ve got a column of dates formatted ddmm in a .ots file that’s a field in a mail merged writer document.

The dates display as desired in the spreadsheet, but when the mailmerged document is printed, the dates are printed as dd/mm/yy. The year part is sensitive and it’s important that it not be printed.

It’s as if mail merge can’t support a user-defined format. Yet when entering the date values in the spreadsheet originally I was unable to find a way to display just date and month as ‘1 Jan’ (and not year), without defining the new format and without having to enter a fictitious year anyway, in which in any case the year is still there.

If 1 Jan could be entered as 1/1, and that supported in the spreadsheet and across Libre Office and in mailmerge, it would be great.

Is there a workaround?

mikekaganski · February 2, 2024, 4:19am

Is there a bug report for it, with a reproducing sample?

Wanderer · February 2, 2024, 6:30am

Internally dates are represented in spreadsheets like Calc as “days since beginning of the 20th century” (actually 30.12.1899, if remembering correctly). So there is no date without year, even if you can tell calc not to show the year it calculates.

Lupp · February 2, 2024, 12:05pm

What about something mimicing a date and looking like “29-02”?

See also xkcd: ISO 8601 .

(Let me decide myself what I want to post and if it is “too similar” to something else!!)

Wanderer · February 2, 2024, 7:10am

No, but you searched at the wrong side of the import.

In the Writer document you can use the context menu of the imported field to customize formatting. My screenshot shows a german localisation but I guess you will find the appropiate entry. Here I chang format for “Birthday”.

The other option would be to import a prepared column from Calc. You may create a Text-column, filled via formula like =TEXT(B2;"DD-MM") and then import this instead of the “real” date. (I also tried this with the <Short> field.)

usrrr · February 17, 2024, 3:15am

Ah I didn’t think of that. It’s all over now, but for the next project, when I have the same issue, I’ll try what you suggest.

Thank you all for your responses.

nordicpa · August 24, 2024, 9:52am

[Wanderer] I am quite certain I have successfully used this “Mail-Merge Field User-Format” (as in your screenshot “December”) in the past, but the current Debian stable LibreOffice 24.2.5-1~bpo12+1 ignores date field’s chosen user format and always prints boring default 12/31/99.
I wonder if is this due to LO-source vs LO-Debian or LOv7 vs LOv24. I sadly have no time to find out , even when this bug forces me to create an additional mess in the MailMerge database to avoid it as in your second suggested option.

nordicpa · August 25, 2024, 1:42pm

Great discovery.
I found out that using calc-table (ods) directly as a source of date field works with many date formats correctly.
It seems my experience of not being able to change the date fields format in MailMerge (MM) documents was due to my start of use of databases (.odb) → linked to the real calc-table (.ods).
Good old direct MM-source from a table file (*.ods) still works great for date-fields formatting.
I now make a soft link to the “MM.ods” file in an absolute directory /tmp/… so it is always there no matter where (folder) I am creating different MM documents from.