hello @James: acc. to the ‘standards’ of this site your comment shouldn’t be written as an answer, but as a comment to or edit of your original question, afaik you can still change that,
reg. ex$el working better i had only 2010 to try, which had similar handling as LO, and i’m curious how any version should be able to solve the ambiguity of the dates in the last three lines …
Thanks - This forum is limited in how to post and respond, so I was trying to find a useful place to reply and thank those who replied. There’s still no place to thank those who replied, so I’m just making do with what’s available.
Same spreadsheet, similar question how-do-i-format-a-column-of-calendar-dates-in-calc
Easier to import all the PayPal .csv files into a new spreadsheet correctly than to try and fix each date manually. I can only re-iterate that you have have to identify the existing date format, Date (DMY) in the PayPal .csv, rather than the date format that you want to apply
Quoting @JamesBurke:
I have to assume that this question is going nowhere, and I thank everyone who tried to help. MS Excel can handle the format issues, and LibreOffice can’t, so I’m just going to switch to Excel because I still have to get my work done at the end of the day. Many thanks, everybody!
The question would clearly have a working answer if there were consistent data (dates) Having to regard (among others) the three examples posted with the question
(1.) 08/13/2019 14:43:13 PST Completed USD 68.21 -2.28 65.93
(2.) 21/03/2019 11:03:24 PDT Completed USD 83.74 -2.73 81.01
(3.) 05/04/2019 12:35:13 PDT Completed USD 30.00 -1.41 28.59
we needed to point out that neither a human brain nor a spreadsheet software can reliably make valid data from this without having access to additional information.
Trying to reading the first column as dates we find:
If Excel actually conceals these facts, and returns something without any hinting to the ambiguities, you cannot get your work reliably done by it.
This is not Excel-bashing! To the contrary I precisely showed in the example attached to my second comment on the question that also LibO V7 treated the data in an unacceptable way in one of the demonstarted cases.
You thanked “everyone”, but you didn’t do what was actually necessary when taking part in a kind of forum: You didn’t resolve the contradictions and/or ambiguities and/or typos…
You didn’t even point out which one of the thinkable formats you actually expected to be applied with your examples.
I therefore cannot accept the thanks.
(I also sometimes have something to be done at the end of the day - and at tghe end of of my days, too.)
I solved this by loading the csv into Excel. The three defective dates immediately showed up and could be fixed by hand. I admit that the problem would be much easier to deal with if L-O was able to crunch the dates easily as Excel is able to do. FWIW, Google Chrome has exactly the same issues, so I’m guessing the O-O, L-O, and Chrome are all based on the same foundation.
OOo (Now AOO and LibO are both based on StarOffice (which has a long history). Chrome is a WebBrowser. Their GoogleSheets is an independent.spreadsheet software for online use only.
To find wriongly formatted textual dates in a list is easily done if the correct format is clearly specified, but neither was this your question nor did you tell what format you expected your dates to be.
Once again with fresh words:
A CSV actually containing a column where MDY and DMY dates are mixed can never be interpreted reliably by what software ever, as soon as it also contains entries not allowing .the needed disambiguation.
Such dates are many (132 every year) - and three of them occured among your examples. .
Right from the start I was asking how to format dates as “Y-M-D format” to remove the ambiguity, in the third sentence. FWIW, the Google Spreadsheet has the same result as O-O and L-O, regardless of it being an online service. I notice that others have been trying to solve this in forums for years. I wish now that I’d tried Excel in the beginning, it would have saved everyone so much time & energy, but Excel is not easy to find.
Well, you don’t still look for help, and my intention to “teach” you and other visitors to this thread concerning an aspect I regard relevant, seems to be bound to fail as is again clarified by your mention of a target format in place of the needed disambiguation of the source format.
Therefore: Over.
As a consolation: There are sayings that even renowned scientist fed their data into Excel sheets and published the erroneous results.
What a mess!
OP had been asked several times to clarify. It seems that OP gets some garbage in, and wants to sort it out manually (my guess) - and they are satisfied by something that they see in Excel, and do not see in LO. I still do not understand from the discussion, what is that, and thus cannot figure out how to help the person who cannot describe it like
I open this CSV [file] in Excel, and see this [screenshot], and then I can do this in these invalid cells; but in Calc, I see this [screenshot], and then I can’t do the same …
Other questions - e.g., this - seem to just repeat the same pattern.
Generally “this” isn’t a LibreOffice issue but a user issue, and I cannot fix users nor suggest workarounds. Not to consider reasonable suggestions is the main symptom of the mentioned issue. Sorry!
However, “software” and “everyday purpose IT” did a lot to enforece “world-as-it-is-vulnerability” by preferring the worst standards in many cases. Using date formats as an example I can tell, e.g. that all(?) the felt hundreds of locales meanwhile supported have a default date format they apply automatically if a date was “recognized” - and there isn’t one of these locales using a reasonable format for the purpose. Even worse: You cannot change that locale dependent default format. (I don’t know exactly since what version at least dates entered in full ISO 8601 extended are no longer reformatted to the bad locale default. They once also were.)
To hell with outrageous localization! Who forces it upon us? To hell with them, too!