Date: prev next · Thread: first prev next last
2015 Archives by date, by thread · List index


P.S. to the special case where you imported "normal" dates in US context
ending up with a column of wrong dates and text.

Assuming that you don't have the original data at hand so you can not repeat
the data import with appropriate import options, this is how to correct
wrong values:
1) Switch the locale option under Tools>Options>LanguageSettings>Languages
from English(USA) to English(UK).
Now the wrong date 1/2/14 switches to 2/1/14 in UK context but it is still
the same wrong date (2nd of February).
The text values like 13/1/2014 remain the same text values but now they show
a leading apostrophe in order to mark them as literal text and prevent
numeric evaluation. In UK context 13/2/14 would be a correct date but the
actual cell values remain stable when playing with language options and
formattings. This is an important feature. It is not a bug by any means.

2) Apply the following formula to the entire date column (assuming dates
starting in A1):
=IF(ISNUMBER(A1);DATE(YEAR(A1);DAY(A1);MONTH(A1));VALUE(A1))
The formula returns the corrected date if A1 has a wrong date and converts
the  text in A1 to a number if the A1 has a text value. The conversion from
text to number happens in the globally set UK context set in step 1). Don't
panic if the converted text values are shown as integer numbers. These
integer numbers are the correct values.
Copy the formula column, select the source column (A in this example) and
paste-special values only (no formulas).
Reset the locale back to US. Now the correct dates switch from 13/1/2014 to
1/13/2014 but they are still the same correct dates.
Format the cells to your liking. Notice that the number format dialog lets
you override the locale option for the selected cell. This has no influence
on the actual cell values which are the right ones after this conversion.



--
View this message in context: 
http://nabble.documentfoundation.org/Formatting-a-date-tp4140660p4140849.html
Sent from the Users mailing list archive at Nabble.com.

-- 
To unsubscribe e-mail to: users+unsubscribe@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

Context


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.