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

office76#xt wrote
      I have a column of dates in Calc that look like this:


Any way to reformat the Date column into some Date format that would allow
conditional formatting as just described? Any ideas welcome.

Given 01.07.1986 in cell A1 these entries in the indicated cells will give
you a valid date:

- in B1 =LEFT(A1;SEARCH("[0-9]\.";A1)) will return the day "01".
- in C1 =RIGHT(A1;LEN(A1)-SEARCH("\.";A1)) will return the remaining data
- in D1 =LEFT(C1;SEARCH("[0-9]\.";C1)) will return the month "07".
- in E1 =RIGHT(C1;LEN(C1)-SEARCH("\.";C1)) will return the year "1986".
- in F1 =DATE(E1;D1;B1) will provide a valid date, which can be formatted as

You can then copy column F data over column A (paste special > Selection of
"Date & time") and delete the other (now unnecessary) columns as required.
Best wishes, Owen.

View this message in context:
Sent from the Users mailing list archive at

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


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.