Libreoffice Calc: date string to date/time values

Question

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.

Answer 1

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.