Description: I wanted to import text from a "Dir" output, and manipulate the dates.. 1. The "import" dialog firstly split the text at every 'space', and won't allow multiple spaces to be 'concatenated', so it split the date & time, and it's not easy to recombine! 2. I copied the output into a text file and edited it to replace spaces with Tabs, and tried again. If I select "D?M?Y" for the first column, it doesn't work, so I chose "text". I checked that the dates HAD been interpreted OK, but trying to format them as "D8" (..HH:MM:SS) generates an error. But if I use a formula to 'copy' the cell, it works! See attached file. Steps to Reproduce: 1.import date/time ("09/06/2023 11:34"), or concatenate a date & time using a formula (=A2 & " " & B2) 2.use the "D/M/Y" option on the import or try 'format cells [D8]' (or "=TEXT(A2,"DD/MM/YYYY HH:mm:SS") 3.Format D6 (HH:MM") seems to work! Actual Results: ### Expected Results: 09/06/2023 11:34:00 Reproducible: Always User Profile Reset: No Additional Info: [Information automatically included from LibreOffice] Locale: en-GB Module: SpreadsheetDocument [Information guessed from browser] OS: Windows (All) - Win.11 OS is 64bit: no - YES! i3 CPU
Created attachment 187971 [details] calc sheet showing the problem(s) I wanted to see if I could extract DIR data for some files with the wrong date, to work out what they should be, with a view to renaming them automatically, but had a few problems, with "import", and "format". See the comments on the sheet. I would have liked to be able to use a TEXT("MM") formulae to extract 'month' or 'minutes', or to use "YYYYMMDD_HHMMSS", but couldn't! I ended up having to use TEXT(Day(G2),"00"), etc. and concatenate! - and got several 'suggestions' when I missed out brackets or quotes, that weren't right) - the files were created by my "wildlife trail camera", which timestamps pictures and video clips, and 'sets' it's clock from a text file, and creates a new one with a default date string.. fortunately that file did have the correct create date! Otherwise I could use the date I intended, as the 'start date'. I'm not sure if I can do the rename operation in code within Calc, or else I will replicate it using Python. Nigel Lee
I'm sorry to say that nothing in the prior comments makes sense, other than perhaps there is some lack of knowledge and/or experience using Calc. The import dialog allows concatenating separators, and it also allows (multiple) space characters as separators, among others. Additionally, the # marks in attachment 187971 [details] (e.g. cell A65) are just indicating that the width of the column is too narrow so as to show the values in their current format; just make the column wider. Please next time search for info; the online help, users' mailing lists, https://ask.libreoffice.org and even a web search can be useful. I'm closing this as NOTABUG.