Date format and concatenation in LibreOffice

John D K -

Problem

Transform multiples dates from simple text format:

12/31/2017 40.45
12/30/2017 1.67
12/29/2017 41.60

to more complex format:

"2017-12-31 00:00:00", 40.45
"2017-12-30 00:00:00", 1.67
"2017-12-29 00:00:00", 1.60

with LibreOffice Calc.

Solution

  • Create new document in LibreOffice Calc
  • Paste the values CTRL + V in two columns
  • Column A with 12/31/17 and column B with 40.45
  • if needed use Text To Columns Wizard
  • Data
  • Text To Columns
  • Select Separator - in my case tab
  • Copy and paste the date in column C - format mm/dd/yy
  • Split the date into days, month and year in 3 columns
  • Text to Column
    Select separator /
  • Finally you should have columns C, D, E with

12 31 17

  • Make new date in Column F - yy-mm-dd
  • use formula
=DATE(C1,D1,E1)
  • Add the time to this date by using formula
=""""&TEXT(F1,"yyyy-mm-dd")& " 00:00:00"", "
  • Add the value with:
=J1&B1

The result is: