We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

how to format a calc page header - special date formats and formula

asked 2020-07-03 14:58:30 +0200

ROSt52 gravatar image

I'm running LibO in English and have Japanese writing installed and this works.

In the page header, date format is by default MM/DD/YYYY.

The format YYYY-MM-DD is already fine, but preferred is the Japanese format YYYY年MM月DD日.

Q1: How can I change the format of the date?

In the page header set up window there is a remark towards the bottom, which states that also formulas for date, time etc can be inserted.

Q2: How do these formula need to like?

Thanks in advance for your help!

edit retag flag offensive close merge delete


Very strange! You can customise the date format everywhere except in the header/footer. Also there is no way to choose a user-defined default for date cells (which could be a workaround for the inability to set a format in the header). The header/footer dialog has many shortcomings.

ajlittoz gravatar imageajlittoz ( 2020-07-03 15:13:42 +0200 )edit

The only reason I can imagine for using Header/Footer for spreadsheet printouts is the pagination. There is no adequate alternative.
Everything else is better placed, imo, in one row (or a few rows) set to be repeated on every page.

Lupp gravatar imageLupp ( 2020-07-03 16:15:16 +0200 )edit

An awkward alternative is to insert Sheet Name field in the header, and rename the sheet (Alt+click) each time with "2020年07月02".

LeroyG gravatar imageLeroyG ( 2020-07-03 16:31:37 +0200 )edit

@LeroyG: manual for manual, I personally prefer to insert the text directly into the header. OP is looking for an automatic solution.

ajlittoz gravatar imageajlittoz ( 2020-07-03 17:37:12 +0200 )edit

@ajlittoz: Yes. I understood the question. My comment does not have the solution (that's why I didn't put it as an answer); but could be a step for someone who know more than me (may be someone who know how to change automatically the sheet name). By the way, changing the sheet name takes a few steps less than editing the heading.

LeroyG gravatar imageLeroyG ( 2020-07-03 18:02:14 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-08-17 02:49:40 +0200

ROSt52 gravatar image

Hi all,

I don't know if this Answer field is the right one for my answer to your comments; I am bit out of this forum since a while.

Thanks to all of you for your comments.

At the end your comments triggered the idea for the solution. I am running LibO with US English, thus get the US date formats as default, which I personally don't like. Maybe I should us a different English version. Prefer one with US spelling but a non US date format. It would be great if languages would not necessarily have an unchangeable connection to basis settings like dates, thousand separators, decimal signs, etc.

As the date format cannot be change in the header/footer, it seems to me that the Japanese LibO version could be the solution. The date format needs to be change in a template. So I have 2 possibilities: 1 - I install the language pack for Japanese LibO, switch over to Japanese LibO, open the template, it becomes an ODS file in Japanese and save it again as a template in the Japanese version. 2 - Send it to a Japanese collaborator to do the same as described above in the normal instance of Japanese LibO.

Should this Idea not work, I will try the comment of @LeroyG.

edit flag offensive delete link more


If you change your Locale setting to Japanese in Tools > Options > Language Settings > Languages won't that give you the date format you want in Excel? Note that you might have to change your currency to suit.

Or maybe, just add the date format in the Date Acceptance field on the same page of the Options dialog? Hmm, the red background to the field when I do that probably means adding a Japanese Date format to English isn't a good idea. Maybe the Locale thing is best.

Earnest Al gravatar imageEarnest Al ( 2020-08-17 03:43:52 +0200 )edit

{US date formats}...which I personally don't like. ... It would be great if languages {locales} would not necessarily have an unchangeable connection to basis settings like dates, thousand separators, decimal signs, etc.

Even having studied the API and much more, and accepting the need of programming, I couldn't find a way to replace the default date format of a locale by a better one. (I only can kill it without a replacement.)
There should be an ISO(relieved) variant to every locale, at least, if it is assumed impossile to get users to be less stubborn concerning their "national" (regional...), mostly stupid preferences.
After all, we live with the outdated sexagesimal system concerning time and angles for >4000 years now.
What is the actual "conditio humana"? Are we at the edge? Will we leave the issue to "AI"?

Lupp gravatar imageLupp ( 2020-08-17 13:31:54 +0200 )edit

Thanks to both of your comments. My reply is a bit late but...

I completely forgot that in Libo Language, Local Settigs, etc are very idenpendet. This is one of the strong featueres of LibO.

ROSt52 gravatar imageROSt52 ( 2020-12-22 12:30:57 +0200 )edit

I'm using en-UK UserInterface and en-UK locale with a few special settings, but I also dislike the default date format I get this way (DD/MM/YY).
I even tried Esperanto for the locale. It uses a most-significant-first default date format but with two-digit-year: YY-MM-DD!
The inevitably ruling rule for humans seems to be
Never do something the reasonable way!
Where possible at all, trap others by misunderstandings!

Lupp gravatar imageLupp ( 2020-12-22 12:54:58 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-07-03 14:58:30 +0200

Seen: 202 times

Last updated: Aug 17 '20