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) Editfindreplace...
[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 )
ViewHighlightValues[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: users+unsubscr...@global.libreoffice.org
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
