Date format resists conditional format

users
#1

Hi,
      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. 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.

But for some reason this won't work and I assume its the dots in the date
cells.

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

#2

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.

#3

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