Ask Your Question

Problem with converting date

asked 2019-09-26 17:46:18 +0200

bartmanpl gravatar image

I have LibreOffice Calc sheet with column with date and time of football match start imported from the internet. Dates are written this way 1/9/2019 13:30, 10/9/2019 10:00, 10/10/2019 or 9/10/2019 16:00

How to convert them to date format YYYY-MM-DD HH:MM so i can sort them? Do anyone have any ideas?

Regards Radek

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2019-09-26 18:08:38 +0200

Opaque gravatar image

updated 2019-09-26 18:11:18 +0200


If you have correct dates and not text - you always can sort them, since date and time are stored as integers + a fraction of a day (=decimal numbers). Thus sorting real dates and times reduces to sorting decimal numbers, regardless of the format.

If your dates don't correctly sort, you don't have dates but text (which is probably your real issue). Select the column and change the format to "Number" and Format Code 0.00000000 (Decimal places 8, Leading zeroes 1). If you now find entries which do not turn to a decimal number, then these are text and no real dates (Revert the format back).

If text is your real issue, then

  • Select the column with you textual dates
  • Go to Data -> Text To Column
  • Select from Column type the correct date format your "textual dates" appear
  • Click button OK
  • Perform "Decimal number format"- check from above again

Hope that helps.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-09-26 17:46:18 +0200

Seen: 56 times

Last updated: Sep 26 '19