Ask Your Question

Difficulty formatting Date as DD-MMM-YY

asked 2019-04-29 04:11:25 +0200

cbgjr gravatar image

LibreOffice Version: Build ID: 8f48d515416608e3a835360314dac7e47fd0b821 CPU threads: 4; OS: Mac OS X 10.13.6

I am having difficulty in formatting cells to have date as DD-MMM-YY. Occasionally I can get the formatting to 'take."

Most of the time when I type in the formatting code it shows it correctly until I type in the final Y, whereupon the formatting reverts to the default MM/DD/YY.

Even using formats I created which show up in the list of available date formats as 31-Dec-99 and 31-Dec-1999 results in the date being formatted as MM/DD/YY

Using the formatting paintbrush doesn't change the formatting to match the "correct" DD-MMM-YY.

edit retag flag offensive close merge delete


Your problem might be related to Tools -> Options -> Language Settuings -> Language -> Option: Date acceptance patterns. If the date value entered on input is not recognized as a date, then it will be stored as a text. And the cell's text-type property does not change when you change the format of the cell. It will stay type "text" as long as you do not convert "text" to "date". Hint: Watch the alignment of the input (if you did not change the default alignment). If the value is aligned to the left, then it is text. If it is right aligned on input, then it is recognized as date (in fact a number).

Opaque gravatar imageOpaque ( 2019-04-29 09:01:06 +0200 )edit

I never saw a strange behaviour as described. It may even be a bug in the very fresh version 6.0.
(Spoken aside:) Why do people insist on inventing stubborn date formats again and again, often one I never saw before in my 74 years of life. Dates should be treated as interchangeable data on any level, at least if software is used, and surely without exceptions in spreadsheets. The one and only globally unmistakable date format is YYYY-MM-DD as specified by ISO 8601.
See also (Thanks to jrkrideau.)
By the way: Even the wise creator of xkcd uses a mistakable vertical line in place of an unambiguous digit "1". Are we completely lost?

Lupp gravatar imageLupp ( 2019-04-29 12:15:15 +0200 )edit

The "vertical bar" is what US-Americans write for the number one in hand writing. In fact if you add the little tick on top left for 1 they may misread it as 7 (which does happen with bank accounts or currency amounts). Yes, they are lost ;-)

erAck gravatar imageerAck ( 2019-04-29 15:27:58 +0200 )edit

A glance in the mirror: This askbot site uses MMM" "DD" '"YY which is a promising candidate for the most ridiculous "standard".

Lupp gravatar imageLupp ( 2019-04-29 16:43:20 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2019-04-29 15:36:04 +0200

erAck gravatar image

You have to apply such DD-MMM-YY date format to a cell, either before entering the date (in which case the format stays) or after. If you enter 31-Dec-99 in a cell that is not formatted, i.e. General, then the locale's default date format is applied, which for en-US is MM/DD/YY.

edit flag offensive delete link more

answered 2019-04-29 19:28:23 +0200

cbgjr gravatar image

The answer came from the hints in Opaque's Comment.

I went back to the CSV file I was using as input. Upon opening the file, I selected the columns containing Dates and changed the column formatting from 'Standard' to 'Date(M/D/Y)'. {Originally I had just changed them to 'Date'.}

With this change, I was able to select the columns containing dates in LibreOffice and use Format>Cells>Numbers and enter my formatting, DD-MMM-YY, and the formatting worked.

edit flag offensive delete link more


Could had helped if you mentioned from the beginning that you were importing CSV, and what your standard default locale was..

erAck gravatar imageerAck ( 2019-04-30 13:50:44 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-04-29 04:11:25 +0200

Seen: 487 times

Last updated: Apr 29 '19