LibreOffice Calc automatic date formatting workaround

I was given a spreadsheet .xlsx file which had dates formatted as “YYYY-MM-DD HH:MM:SS” and I could not figure out how to get LibreOffice Calc to stop formatting it as a date while preserving the date. When I formatted it as text it became a FLOAT number, but that’s not what I wanted. So the solution was a workaround, although simple, it took some time and was satisfying to make work.

I selected both columns, deselected the headers, right-clicked and cut the two columns of dates. Then pasted it back by right-clicking, hovering over “Paste Special”, and selecting “Text”.

Here is a snippet of the situation:

tripdurationstarttimestoptime
364192017-09-01 00:02:012017-09-01 00:08:05
35700:08:122017-09-01 00:08:122017-09-01 00:14:09

The “19” is a result of using the LEN function to get the length of the string, which before when it was formatted as a date was returning 15 because it was ignoring the hyphens and colons for some reason. And the “00:08:12” is a result of the RIGHT function returning the 8 characters from the right side of the string.

Once I did this, I could reformat it as a date and format it back to a text string without any issues. I also found that pasting it into another spreadsheet program such as Google Sheets strips the formatting so it becomes a raw string while preserving the original text as well. I suspect there was some formatting incompatibility initially. This must be due to incompatibility between formatted .xlsx files and .odt files in LibreOffice Calc.


Comments

Leave a Reply

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