Description: load a CSV with dates in an unconventional but obvious format. It is not easy to recognise this field as a date and reformat it. It should possible to when formatting a cell as date to specify the current format that the source data is in and a desired target format. For example, in a sheet with the following dates: 20070320 20070319 20070316 20070315 20070314 20070313 20070312 20070309 20070308 These are obvious to a human as "YYYYMMDD", but there is no obvious way to get Calc to recognise and reformat these dates. Steps to Reproduce: 1. create a new calc sheet with the following data in a column 20070320 20070319 20070316 20070315 20070314 20070313 20070312 20070309 20070308 2. attempt to convert (format cells) that into a date format in YYYY-MM-DD format. It doesn't handle the date correctly 3. Actual Results: Convert the date into the desired format Expected Results: it converts it into an invalid date Reproducible: Always User Profile Reset: No Additional Info: Version: 7.1.0.3 (x64) / LibreOffice Community Build ID: f6099ecf3d29644b5008cc8f48f42f4a40986e4c CPU threads: 16; OS: Windows 10.0 Build 19042; UI render: Skia/Raster; VCL: win Locale: en-GB (en_GB); UI: en-GB Calc: CL
This is not a bug. A number like "20070320" is a number. It cannot be treated like a date, because it would break working with numbers. However, you might import the column as text, and then post-process the data in it using things like =DATE(LEFT(A1;4);MID(A1;5;2);MID(A1;7;2)), or to split the imported column using Data->Text to Columns with "Fixed width" option, to separate the parts of the date.
By the way, this is https://xkcd.com/1179/.
Another way to post-process a column of such dates imported as text: select the column, use Find and Replace dialog (Ctrl+H) to search for "(\d{4})(\d\d)(\d\d)" (without quotes), and replace with "$1-$2-$3" (also without quotes); "Regular expressions" and "Current selection only" must be checked under "Other options" in the dialog.
I considered marking this as an enhancement but thought it looked like a bug. The suggestions you have shared indeed work for converting the "number" into a date, but if it's technically feasible, it may be something that's worth making easier to accomplish via the cell formatting workflow. Thanks for your feedback, and I think your resolution is acceptable. I'm happy to re-open this as an enhancement/suggestion instead if you think it makes sense.