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

Ask Your Question

How to change the default date format in Calc to show 4-digit years? [closed]

asked 2013-11-02 11:41:40 +0200

MrP gravatar image

updated 2021-05-27 13:37:38 +0200

Alex Kemp gravatar image

I'm set to a UK locale and the dates are formatted correctly in that respect. However, the default format is for a 2-digit year (DD/MM/YY), and I would like it to default to a 4-digit year (DD/MM/YYYY). Is there anyway I can set this as the default?

Despite typing 4-digits (eg. 02/11/2013) it always reverts to the 2-digit format, until I manually set the formatting for that cell or column. Unfortunately I'm having to do this rather a lot it seems and it's quickly becoming tedious.

Format cells dialog

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2021-05-27 13:37:49.952003

1 Answer

Sort by » oldest newest most voted

answered 2013-11-02 12:24:48 +0200

oweng gravatar image

updated 2013-11-02 12:31:21 +0200

You can't change the default format. It is hardcoded to the locale of the system and all of them use a 2-digit year by default. The reason is that there are simply too many default settings to allow even a modest percentage of them to be displayed via dialogs. This question is related.

The only workaround is to:

  1. Create a cell style that uses the required display format.
  2. Save this empty sheet as a template.
  3. Set this template to be the default.

You can then manual apply the required cell style to each cell containing a date as required. Bugs fdo#30821 (Sorry, Writer table context rather than Calc) and fdo#41044 are related.

edit flag offensive delete link more


"...there are simply too many default settings..." - I have seen this mentioned a few times, but it doesn't seem relevant?! All the different permutations don't need to be listed. There is already a single "hardcoded" default (initially dependent on locale) which needs to be editable. There is an edit box where you can enter an arbitrary format code - this is all that's required. At most there needs to be 1 default for each identifiable data type... Date, Time, Number, etc.?

MrP gravatar imageMrP ( 2013-11-02 14:15:46 +0200 )edit

To be honest, it should at least default to the short date format of the machines locale (which is what Excel 97 does)? However, a user-entered default should then override the locale. There is also the issue of why a "2-digit year by default"!? Anyway, thanks for your suggestion and link, it is helpful. (I would upvote you, but I don't appear to have enough rep.)

MrP gravatar imageMrP ( 2013-11-02 14:41:01 +0200 )edit

FWIW I agree that the default year should always be 4-digits. "All the different permutations don't need to be listed." This is not the problem - the rest of your comment is more accurate re. an arbitrary format code etc. The problem is where to draw the line on what to list in terms of all the different settings in LO (there are 100s if not 1000s), not the values they can take. It is impossible to please everyone, but your point is well made. You should have enough karma now.

oweng gravatar imageoweng ( 2013-11-03 03:08:30 +0200 )edit

I was truly suprised to learn ISO 8601 cannot be used as default date format for all documents. Yes, I understand I can format cells in sheets to use this format, but to not have it available as a default choice must surely be an oversight, Please see: http://www.iso.org/iso/home/standards/iso8601.htm

utiac gravatar imageutiac ( 2016-03-16 21:44:31 +0200 )edit

Question Tools

1 follower


Asked: 2013-11-02 11:41:40 +0200

Seen: 20,594 times

Last updated: Nov 02 '13