[libreoffice-users] Re: Date format resists conditional format

office76#xt wrote
> Hi,
>       I have a column of dates in Calc that look like this:
> 01.07.1986
>
> ...
>
> 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
"07.1986".
- 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
required.

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: 
http://nabble.documentfoundation.org/Date-format-resists-conditional-format-tp4105794p4105811.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