Search code examples
libreoffice-calc

Libreoffice Calc: date string to date/time values

I need to extract a 'date string' as separate columns representing date/time data.

 | A                        | B                 | C
-+--------------------------+-------------------+-----------------
1| Datetime String          | Date Value        | Time Value
2| Sat 09 Sep 2017 20:00 PM | =DATEVALUE(A2)    | =TIMEVALUE(A2)

The above functions DATEVALUE and TIMEVALUE return errors. I'm at a bit of a loss at the moment.

Solution

  • It looks like the date string is fixed width, so use this formula for cell B2, which builds a date string in YYYY-MMM-DD format and then converts with DATEVALUE.

    =DATEVALUE(MID(A2,12,4) & "-" & MID(A2,8,3) & "-" & MID(A2,5,2))

    The formula for cell C2 only needs to extract the time, which is already in a suitable format.

    =TIMEVALUE(MID(A2,17,5))

    Then go to Format -> Cells, and format B2 as a date and C2 as a time.