Difficulty formatting Date as DD-MMM-YY

English
, , , ,
#1

LibreOffice Version: 6.0.3.2
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.

#2

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).

#3

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 xkcd: ISO 8601. (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?

#4

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 :wink:

#5

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

#6

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.

#7

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.

#8

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

#9

(For anyone struggling with this problem in 2022 after looking at the other responses here.)

There appears to be a quirk with how LibreOffice Calc works with Jira Exports. That or I’m stupid! :crazy_face:

If you imported information from a CSV and pasted it into Calc, try the following:

  1. Highlight the relevant date cells
    NOTE: If you do not highlight cells, this will apply to all cells of the entire sheet. Be careful!
  2. Find and replace (Ctrl + H)
  3. In the “Find” field, put a slash (/) without any other characters
  4. In the replace field, put a hyphen (-) without any other characters

If you were experiencing the same issue I was, you should notice that your text shifts from the left of every cell to the right, just as anon73440385 mentioned!

You should now be able to apply whatever date formatting you wish to those cells.

Made a quick video tutorial here: https://youtu.be/MwcQ-FGE4t4

Cheers.

#10

When you are pasting unformatted text, such as from a csv file, you should get the Text Import dialogue box. In it you can specify the separator, and the data type and format text for each field (column). If you do this correctly there is no need for further correction.

See also Text Import

#11

You ignore the options and the help button on the text import dialog. Calc can import many thousands of number formats flawlessly including dates, times, currencies Thai, Hebrow, Arabic, Chinese, even in US-English.

[OK, LO devs refuse to make the infamous “special cells” option checked by default which is responsible for about 10% of all Calc related support requests]

#12

Calc does not know of Jira-Exports and the main problem is: csv don’t carry the necessary information inside the file. So you are asked, how to import.

Your aporoach is useful for Excel, as it is (or was) not asking me but just imports to the own wisdom - where I often disagreed…
One of the points, wich droves me to Openoffice…

#13

I see the Text Import dialogue box when I open the CSV export.
I do not see the Text import dialogue box when I copy/paste that information into a different spreadsheet.

#14

Ah, between your and @EarnestAl’s comments I think I’ve figured it out.

The suggestion you are making is:

  1. Open the export
  2. In the Text Import dialogue box, choose: Comma
  3. Scroll through the preview pane at the bottom and find the date field(s)
  4. Change column type from Standard to Date (DMY)

Do I have that right?

#15

How can we know the correct setting without any data? The following is a typical row from my one of my German bank accounts copied from a plain text editor.

10.01.2022;10.01.2022;"Dauerauftrag Überweisung";"Stadtwerke xxxxxxx";"VKto 2135004 Stadtwerke xxxxxxxx Info: VKto 2135004";EUR;;579;"759,78"

The fields are separated by semicolon.
First 2 columns are German dates DD.MM.YYYY
Last 2 columns are comma decimals.
Some strings are double-quoted. The EUR is unquoted. Surprisingly, the last comma decimal is quoted too. This is a rather silly flavour of csv.
That bank encodes text with Windows 1252. With wrong encoding, the umlauts (“Überweisung”) would be wrong.
I import this row set with import locale “German(Germany)” which takes care of all the number formats (including German month names in dates if there were any).
I check “Detect special numbers” which is the right choice in 99% of all use cases. Otherwise it would import any dates as strings. Unfortunately, this is off when you see this dialog for the first time.
I untick “Quoted fields as text” because there is one field with a quoted number to be imported as a number.

I specify the explicit field type in the preview section only if some csv requires an exception to the above rules. I want to import phone numbers, zip codes or priduct IDs as text. Sometimes you have US dates with comma decimals or non-Dollar currencies.
The settings are saved, so I only have to confirm the dialog when the next csv is a similar one.

1 Like
#16

Maybe I should have bolded “pasting unformatted text”. When pasting from a text only program such as Notepad or some databases you will get the Text Import dialogue. Or click Edit > Paste special > Paste unformatted text if pasting from an already formatted source to get the Text Import dialogue

1 Like
#17

Ah, thank you and @Villeroy for your responses. I think I fully understand where my failure was now. Thank you for being patient with me. :slight_smile:

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.