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:
tripduration | starttime | stoptime | |
364 | 19 | 2017-09-01 00:02:01 | 2017-09-01 00:08:05 |
357 | 00:08:12 | 2017-09-01 00:08:12 | 2017-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.
Leave a Reply