I’m helping someone process a collection of research data that has been entered by a third party using Excel. We’re using LibreOffice Calc, because research should be reproducible by anyone, not just those in possession of prioprietary software licenses (this also means that we use R, JGR and DeduceR instead of SPSS and Statistica; perhaps more on that later).

After having to fix hundreds of badly entered dates with basic functions (we highly recommend that ISO 8601 format dates, i.e. YYYY-MM-DD, are used from the very start, instead of ambiguous local formats), we ended up with a stubborn subset of DD-MM-YYYY formatted dates in cells that explicitly had ISO 8601 format configured.

What could we do to convert these DD-MM-YYYY dates to the ISO 8601 standard YYYY-MM-DD dates?

The trick here is to use LibreOffice RIGHT, MID and LEFT functions to pull the faulty date apart, and then to put it back together in a new column using the DATE function.

In this screenshot you can see an example:

RIGHT(A2,4) extracts 4 characters from the right of 13-12-1981, yielding 1981, MID(A2,4,2) yields the 2 characters starting at position 4, and LEFT(A2,2) gives us the two characters at the left, in other words the day.

Putting all of this together with the function =DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2)) will return the date formatted according to the column format setting (right click column heading, select Format Cells and then the ISO 8601 formatting). Obviously you have to replace A2 with the cell broken date format that you want to fix.

Globally, you would create a new column, use the formula above in its first row to fix the first date in the existing broken date column, then replicate the formula all the way down by clicking on it, and then dragging the rectangle at its lower right all the way down.

(Searching for “LibreOffice convert date formats” was of no help, whereas doing the same for Excel yielded at least two good answers (one and two), on which this post is based. I’m putting this out there so that searching for LibreOffice will also turn up something useful.)