Convert dates to different formats in LibreOffice Calc

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

4 thoughts on “Convert dates to different formats in LibreOffice Calc”

  1. It was very useful to convert a date to a number: I imported from a file dates, times and other infos. I needed to plot it using dates and times and therefore to convert it these to numbers first. Somehow the cell format could not be changed (it refused to change). However, since I wanted to change the format from US to standard too and the info from this post was needed it did both jobs in one:

    10/11/2014 04:45

    DATE(RIGHT(A4,4), LEFT(A4,2), MID(A4,4,2))
    TIME(LEFT(B4,2), RIGHT(B4,2), 0)

    Then, adding both gives: 41923.20

  2. It is incomprehensible that LO doe NOT allow us to set date format defaults. I have for YEARS use the ISO format YYYY-MM-DD. It has worked for me in all versions of MS Word.

    I have my parent OS configured for this same date format!

    Yet when I open any of my documents created in word or excel in LO it reformats my dates AND will not let me change this behavior.

    How are we going to get a sane date format support in this package? I cannot live with this major defect!

Leave a Reply

Your email address will not be published. Required fields are marked *