At 13:14 19/04/2014 -0700, Nobody Noname wrote:
I have a column of dates in Calc that look like this:
01.07.1986
02.07.1986
03.07.1986
04.07.1986
This is a day, month, year format.
You say they "look like" this, which is not entirely helpful! Are
these genuine dates, i.e. numbers formatted as DD.MM.YYYY or are they
text values? The answers are very different.
Normally I could conditionally format the column to say detect all
the days in the column that fall on the 4th day of the month, and
turn the background of all their cells to say, blue.
o In the Conditional Formatting dialogue, select "Formula is".
o For date values, use DAY(Xn)=4 ; for text values, use VALUE(LEFT(Xn;2))=4 .
Any way to reformat the Date column into some Date format that would
allow conditional formatting as just described?
If they are date values, you can reformat them by changing the cell format.
If they are text values, you will have to handle them as such. If you
want to change them, you can rearrange the parts into new text values
- perhaps using the LEFT(), MID(), and RIGHT() functions. Or you
might choose to convert them to genuine date values - perhaps
using the VALUE() function in addition. Another possibility is first
to use Data | Text to Columns... to separate the three parts of the
date and then the DATE() function to recombine them appropriately.
(You'll even need VALUE() this way, I think.)
Another idea is to consider where these values come from: it may be
easier to import them in a more useful format originally.
I trust this helps.
Brian Barker
--
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