How do I format a column of calendar dates in Calc?

[JamesBurke]

So far so good. But I have to know if the date “09/01/2020” is MDY or DMY. It’s not obvious. LibreOffice knows, but it’s not telling. Regardless, if I choose either, and set the format the conversion only works for days less than 13. In other words “09/01/2019” will be properly imported as a date, but “15/01/2019” won’t be because the first number is more than 12. I’m thinking this must be a bug in the import function. For example, an imported column of dates ends up like this:

|____ 2019-09-01| (formatted as “Date”)

|____ 2019-10-01| "

|15/01/2019____ | (not formatted because the day (15) is greater than “12”)

Now the unformatted dates above cannot be changed no matter what I do. This is a nuisance when dealing with thousands of records. Any ideas?

[EarnestAl]

Libreoffice doesn’t know, a csv is plain old text. In the text import filter when you are choosing how the date is formatted you have the possibility of scrolling down the list to an identifiable date, e.g. 8/13/2020. You know then the whole column Is MDY and you can select the correct file type for it.

If the csv happens every month from the same source then the date format will remain the same. Otherwise make a phone call

[JamesBurke]

Thanks - then I’m thinking “Wait! Make a phone call to whom”?! This particular problem puzzles me because OpenOffice can handle the data easily. Since there’s only three date types, it’s also not rocket science, and I’m thinking that LibreOffice might be able to ask OpenOffice how they do it. It doesn’t matter if the data is always in the same format if LibreOffice can’t handle it. It still remains unformattable. Are the LibreOffice developers not speaking to the OpenOffice developers?

[EarnestAl]

Call whoever sent you the csv file. The Text Import window is called that because you are importing text. You have the option to define the field type that is represented in the csv by text. If you tell Calc it is Date (MDY) it will use that pattern when processing the text into data. If it turns out the text field is Date (DMY) then you will get 9th January 2020 instead of 1st September 2020 but when it gets to 13 September 2020 it doesn’t fit the filter so it imports it in its original format as text.

If you don’t define the field type then there is a process where Calc will try to determine what sort of type it is. If it gets it wrong because, say the first entries in the column could be either, then you will have a problem whereby just text has been imported for much of the column.

In a csv 1/9/20 is just that, text. In a spreadsheet it is a number, 44075 but formatted to display as a date.

[JamesBurke]

The files come from monthly PayPal reports, and PayPal quite rightly says nobody has a problem who uses Microsoft Office or OpenOffice to process the files. Only LibreOffice has a problem with PayPal csv files, and that it’s not a problem for PayPal, it’s something that LibreOffice has to fix. I can see that OpenOffice zips right through the import process with no problems, so I was hoping someone at LibreOffice might be able to update or fix their wonky import filter.

[mikekaganski]

I bet the “problem” here is use of a locale, that is set for OP to some different value in LibreOffice than in OpenOffice. That makes an impression that OpenOffice “can”, while it just uses the set locale, which happens to coincide with the order used in the CSV.

[Villeroy]

Sorting dates in columns

I am trying to sort dates in a column of data. The filter does not seem to recognize date format and sorts the data by a criteria that I cannot understand. I have changed the cell format to date yet it mixes years 20/21/and 22 ? Any suggestions please

[EarnestAl]

The Text Import window allows you to identify the data type for Calc. It is a good idea to always define the data type for dates. If the csv contains dates such as “09/01/2020” that you know is 1st September 2020 (as above) then it must be imported as column type Date (MDY). If the filter is set as another date type you will import only the original text, not dates

It can be subsequently formatted as YYYY-MM-DD but if you mis-identify the type during import it gets imported as just the original text.

Edit 2020-08-012
Open a new spreadsheet in Calc, in the menu select File > Open, in the dialog that opens select your csv file and click Open.

A new dialog window will open called Text Import, in this you need to select the delimiter (if it already shows the text in columns then you are probably OK), make sure “Detect special numbers” box is ticked so the money is imported as money. In the column for Date you need to tell LibreOffice what the format of the date is (it can’t tell if 1/1/19 is DMY or MDY) so click on the column and then you can change the Column type to Date (DMY), OK out.

Having successfully converted the text to dates and dollar amounts you can change the date column to your desired format, in your question you wanted YYYY-MM-DD. Select the date column, in the menu click Format > Cells..., in the dialog that opens select the Numbers tab, on the left hand side, Category, choose Date, in the next column, Format, scroll down and select 1999-12-31, OK out. Save As [SomeName].odt

[JamesBurke]

As explained above, this doesn’t work. I thought it was explained. However, thanks for your reply.

[EarnestAl]

I assume PayPal formats its dates in US format so the Text Import filter for the CSV must be set to Date (MDY) for all date columns.

[JamesBurke]

I think that the whole point of this conversation was that it doesn’t work. It works in OpenOffice, but not in LibreOffice. Was that not explained above? But thanks for your reply.

[nobody_special]

Are you saying that the PayPal CSV date format VARIES between MDY and DMY in the same file?
Can you submit a CSV file that works in Office or OpenOffice but fails in LibreOffice?

CSV is a comma separated value text file and assumes that all values are text, with no special formatting applied to any entry. Dates are also in text format and if you want a consistent sortable date field, you need to have the format identified during import (is it MDY, DMY or YMD?). Then you can format the resultant column to your own format, such as YYYY-MM-DD.


Is 04/06/2020 = April 06 2020 or is it = 04 June 2020? If the format changes within the same file, how can a person, let alone LO determine which format is correct for each entry?

[JamesBurke]

It’s not the csv file that’s the problem since Excel and OpenOffice can handle the import (Excel much more easily than OO). But LibreOffice is unable to process the import properly, leaving the dates broken or incomplete. To me it looks like a LibreOffice problem, not a csv problem. Here’s a better example:

Date Gross Fee QTY Ship Net
2019-01-01 $177.40 -$6.86 12 $10.00 $170.54
2019-11-01 $309.05 -$11.73 19 $32.00 $297.32
2019-12-01 $30.00 -$1.41 1 $0.00 $28.59
19/01/2019 $30.00 -$1.41 1 $0.00 $28.59
22/01/2019 $85.60 -$3.47 8 $6.00 $82.13
26/01/2019 $30.00 -$1.41 1 $0.00 $28.59
2019-02-02 $30.00 -$1.41 1 $0.00 $28.59
2019-08-02 $30.00 -$1.41 1 $0.00 $28.59

It may not show here clearly, but where the DAY is “12” or less, the date can be formatted, but the format is wrong (as YYYY-DD-MM), and it can’t be formatted any further. L-O moved the YEAR to the right place, but left the other characters in the wrong place. The dates are now unusable.

[EarnestAl]

According to Paypal Forum the date codes since sometime in 2018 are in dd. mm. yyyy format. This format appears to match the unmodifed text dates in the text above, e.g. 22/01/2019. Therefore the date columns must be imported as Date (DMY) .

Ignore your current spreadsheet. Import the .csv into a new spreadsheet with the date column as type Date (DMY). When, and only when, that is imported into the spreadsheet you may change the format of the dates to anything you like.

[JamesBurke]

I can import the file OK, but not sure what you mean by “date columns must be imported as Date (DMY)” ? I don’t see any choice for “importing as Date (DMY)”. Where do you see that? Once the file is imported, I can’t format the dates column. Here’s what I get:

Date Gross Fee Net
01/01/2019 $177.40 -$6.86 $170.54
03/01/2019 $22.95 -$1.15 $21.80
11/01/2019 $309.05 -$11.73 $297.32
12/01/2019 $30.00 -$1.41 $28.59
19/01/2019 $30.00 -$1.41 $28.59
22/01/2019 $85.60 -$3.47 $82.13
26/01/2019 $30.00 -$1.41 $28.59
02/02/2019 $30.00 -$1.41 $28.59
08/02/2019 $30.00 -$1.41 $28.59
09/02/2019 -$11.95 $0.00 -$11.95
09/02/2019 -$16.42 $0.00 -$16.42

I can format the numbers (into $ for example), but I can’t see how to format the dates into something useful.

[ve3oat]

"I can’t format the dates column"

Why not? Once the file is imported, have you tried to highlight the column of dates, then use menu Format → Cells…? Then under Category choose Date and an appropriate particular Format. Sometimes, I also have to choose Language = English(UK) to accomplish what I need, so you might have to choose something other than English(US).

Added – Also, when you perform the importation, be sure to check the Recognize Special Numbers box, in order to make sure the dates are recognized as something other than flat text.

[EarnestAl]

@ve3oat There is a dangerous mix of date formats in the existing spreadsheet file. You can see that the 11th January 2019 has been imported as 1st November 2019 in the data given in the third comment above your comment. This is why it is easiest and fastest to start again from original data and import it correctly. Cheers, Al

[ve3oat]

@EarnestAl : Thanks, and I understand about the mix of date formats in the original data – A most unfortunate situation. The OP claimed that he couldn’t format the date column, which should not be the case. In case he does not want to go back and start over, my suggestion will work if he re-formats the column in sections according to the original format. (An unpleasant task, I know from experience.)

[EarnestAl]

@ve3oat Presumably, he will want to import further PayPal data. It’s probably best to know how to import it correctly into a spreadsheet.

[JJJoseph]

Solved! Finally! Thanks to EarnestAl, I’ve managed to wrangle this year’s PayPal summaries into a workable spreadsheet. I just couldn’t locate the solution suggested within the time limits to get the work done. But now, 6 years after fumbling with this data every January, I’ve got it figured out. Now I have to be able to remember it come next January!