Cell background colour changes if date is more than x days?

[celeryman]

Can anyone advise me on how to have cell background change colour if date is more than x days?

Example.

Edit: Added the .ods, both Example A and B columns have the same dates and conditional formatting, but as you can see once applied to the entire Example B column, the dates change.

date-colour.ods

[Zizi64]

You have applied the CF to multiple ranges: A2:B2, A3, B3:B10.

Try to apply it to a contiguous range what you want to format this way. Preformat the whole input cell range. Note: if you want not format the empty cells in the range (what you will fill-in in the future) then you must apply an another condition too.

But never Cut&Paste the CF-ed ranges or cells, because there is a serious bug in the CF function:

https://bugs.documentfoundation.org/show_bug.cgi?id=133027

[Zizi64]

" once applied to the entire Example B column, the dates change."

How you applied the CF to the column B? If you use the Mouse Drag method, then the content will be overwrited.
Just adjust the range reference in the CF panel, or try to use the format paintbrush.

[Zizi64]

Use the Conditional format feature with a custom Cell style.

Apply a this parameters in the CF feature:

The Cell value is
Less than
=Today()-5

Create and use the custom Cell style “MyFormat” (with a colorized bakground) to the case when the condition gives the boolean value TRUE as result.

[celeryman]

I went to Format > Conditional > Condition and set the following, but the date (today) has a colourized background. Perhaps I misunderstood?

[Zizi64]

Not “Formula is” but The Cell value is" option must be choosen. (In this case you can format only that cell where the CF is applied.)

…

Or you can use the “Formula is” option too, but the formula must be:

(TODAY()-5)>A1

In this case you can reference to an another cell too.

[celeryman]

1. There isn’t a “Cell value is” under the Conditional drop down menu, only “Cell Value”.
2. I tried the Formula is method, it works on the first cell but messes up the other dates when apply to the entire column. Forgive me, I only know the basics.

[karolus]

There is a Cell Value is

-> Format -> Conditional -> Condition …

[Zizi64]

“There isn’t a “Cell value is” under the Conditional drop down menu, only “Cell Value”.”
What version of the Libreoffice are you using?
(I am using LO 6.1.6, LO 6.4.7)
See the new image at my answer…

[karolus]

Version: 6.1.5.2
Build ID: 1:6.1.5-3+rpi1+deb10u7+rpt1
CPU threads: 4; OS: Linux 5.10; UI render: default; VCL: gtk3;

[Zizi64]

“I tried the Formula is method, it works on the first cell but messes up the other dates when apply to the entire column. Forgive me, I only know the basics.”

celery man, please upload your real .ods type sample file here (you can do it at your original question…)

[celeryman]

I’ve added the .ods to the original post.