How To Convert a non-standard date format?

[ehbowen1]

I use a freelancer contracting web site which offers transaction record downloads with only one possible date format: MM DDD YYYY (01 Jan 2020). I’m trying to import this download into GnuCash as a CSV spreadsheet. GnuCash does not recognize any date format with the month abbreviated as letters.

I’m able to get LibreOffice Calc to recognize the column of text as dates in that format, not a particular problem. However, I’m completely stumped at how to convert those dates into a format GnuCash can recognize, preferably ISO 8601. Whenever I try reformatting the cells Calc converts them to plain text and no longer recognizes them as dates, and if I try calculating the base date with “Text to Columns” again, it converts the data to plain text and no longer recognizes it as a date.

I’ve just gone through, by hand, and converted every one of those dates. I don’t want to have to do this again. I’m sure there’s a way; I just haven’t found it.

[Steph1]

This formula replaces the spaces by dashes, then uses the DATEVALUE function that tries to identify a date, then finally formats it as YYYY-MM-DD. N46 contains “01 Jan 2020”.

=TEXT(DATEVALUE(SUBSTITUTE(N46," ","-")),"YYYY-MM-DD")

[Villeroy]

Format the dates as YYYY-MM-DD and save the csv file. Per default, the columns are saved in the displayed format.