1. Do not type dates into cells that are formatted as text and do not type dates with a leading apostrophe 2. Do not import dates as text values from csv or html files. 3. When you paste content from another program, then do not hit Ctrl+V. Use the paste-special command, import raw text data and import the date column as dates
With the right method of input/import/paste your dates will sort properly and you can calculate all kinds of time intervals, time differences, aggregate by months, years, weeks etc. Import options for numeric text data when using paste-special or when opening text tables (csv): 1) The "special numbers" option should _always_ be checked. Special numbers are dates, times, booleans, numbers with currency symbols, any kind of number with more than minus sign, digits and a decimal separator. ALWAYS check this option. 2) "quoted numbers as text": Some text files there are quotes around numbers (e.g. zip or phone numbers) in order to mark them as text. WIth this option checked, quoted values are always imported as text. Now for the most important option in the whole dialog: 3) Language. Choose English(USA) if 1/2/2015 should be interpreted as 2nd of January and decimals have a point (3.14) Choose English(UK) if 1/2/2015 should be interpreted as 1st of February. 1.2.2015 looks like a typical German date together with decimals like this: 3,14 (comma decimal). If the file has currency values like ￥3.14 you would choose Japanese. If the file has currency values like 3,14 RUR you would choose Russian import language. 4) the preview table on the bottom of the import dialog lets you select columns and mark them as DMY dates, MDY dates,YMD dates, US English or Text. This overrides all the other options for the selected columns. When you have special numbers checked and disregard any quotes around numbers and now there is a field that looks like "3-4" (meaning "3 to 4" but software can not know what it means) then you should mark this column as text. Otherwise it may be interpreted as special number 4th of March with US language or 3rd of April with any other language. If you have entered/imported/pasted wrong data (numeric text): Formatting will not (and must not) change any data. You need to re-enter all data. But you don't need to do that manually. Semi-automatical method to re-enter all data of the selected cells: 1)Select the column(s) in question. 2)Apply the right number format language and any number format you like to see afterwards. 3) Edit>find&replace... [More Options] [X] Current selection [X] Regular expressions Search: .+ (a dot and a plus) Replace: & (ampersant) [Replace All] Caveat: If you imported dates with the wrong language option, then you may have a mix of wrong dates and text values: 01/02/14 (meant to be 1st of February but the actual cell value is 2nd of January ) 13/02/14 (meant to be 13th of February but the actual cell value is text because in US context there is no 13th month ) View>HighlightValues[Ctrl+F8] shows the text dates in black font and the wrong dates in blue font. In this difficult case you need a formula to interchange months and days for the wrong dates and then convert the text cells into dates. =DATE(YEAR(A1);DAY(A1);MONTH(A1)) turns 1st of February into 2nd of Januaray and vice versa. -- View this message in context: http://nabble.documentfoundation.org/Formatting-a-date-tp4140660p4140844.html Sent from the Users mailing list archive at Nabble.com. -- To unsubscribe e-mail to: [email protected] Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
