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

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+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