How to conditional format background colors depending on date in column A?

[alex5730]

In LibreOffice 25.2.0.3 I have a spreadsheet with columns A-D. Column A has a date, and I want to color the background of the rows alternatively with some greyish color, so that the rows of the next date have a different color, and rows belonging to the same date have the same color.

So I go to Format->conditional->manage and click on Add. Here I fill out the formatting as follows:

but after I apply this the table looks like the following:

1. The date format in column A has been changed from a date to a number
2. Only the column A has a changed background color, but not columns B-D even they have been selected.

Without applying any formatting the sheet looks like the following:

But what I want is something like this:

Can this be made? Or do I have to do that by hand for each day?

[Wanderer]

@alex5730

date format in column A has been changed from a date to a number

Inspect your choosen format “Untitled1” from your first sreenshot: It is used, when the condition is met. So set this format to show dates instead of internal numbers.

[Wanderer]

@alex5730

Only the column A has a changed background

You were in A2, when you wrote your ISODD(A2)
As usual this is a relative address, so for your case use
ISODD($A2) to fix column/ make this part absolute.

[Wanderer]

@alex5730

Can this be made? Or do I have to do that by hand for each day?

Your single condition will only do one colour(after you fixed above problems). The other rows will stay white. So yo need to set the darker gray of your screenshot “by hand” for your table, while the conditional format does the lighter gray.
.
Or you could add a second conditional format with iseven, but it is not necessary, so should be avoided.
.
A problem will arise on your approach, if a day is missing in your list like 2025-04-03 followed by 2025-04-05
Both lines would have the same colour. Check the answer by @PKG to address this.
.
And to answer your question: No need for manual edit, after set up properly.

[alex5730]

Thanks for this input, but how do I change the style so that the first column is a date, the next two are times? I just want to change the background color, not to micromanage the format of each column …

[PKG]

@alex5730

but how do I change the style so that the first column is a date, the next two are times?

Surely you have already formatted the columns beforehand?

Then keep your hands off the number format of the cell template that you want to use for the conditional formatting.

[alex5730]

I have not touched the “Numbers” format at all.

I create a new style and ONLY set the background color. Nothing else. I click OK, and now the dates and times are shown as float numbers.

[PKG]

@alex5730

I have not touched the “Numbers” format at all.

I can see on the screenshot in your new topic on a cell template that the file was edited with Excel, so the cell templates are probably all contaminated with the wrong formats.

[alex5730]

I re-formated the columns with the proper formats, and created the conditional formatting again from scratch. Again, the values were converted to a float number.

When I delete the conditional formatting the background color goes back to white (the default), but the values in the cells stay as floating point numbers …

Maybe there is a bug?

[PKG]

@alex5730

Maybe there is a bug?

Check the Default cell style for number formats.
If there are any, delete them.

If you can’t fix it, please upload a file with the faulty display here.

[alex5730]

I see this:

[PKG]

@alex5730 ,

[alex5730]

And how to remove this “code”?

[alex5730]

I created a new file, and it seems the file I used is “corrupted”. But even when using a new file, now 1. the complete line has grey background, 2. the style is left alone, but only ONE line has this grey background. Even there are two lines with the same date. I am very confused …

[PKG]

@PKG

If you can’t fix it, please upload a file with the faulty display here.

You could comply with the request and we will find the problem using the file.

[alex5730]

Sure, here is the file:

LO_Test.ods (12.6 KB)

[PKG]

@alex5730

Sure, here is the file:

The error was that the range starts with A1, but the condition starts with A2. These references must be identical!

LO_Test (1).ods (17,0 KB)

[PKG]