Conditional Formatting Based on Day Number

[calvin188]

Hi, I’m trying to highlight a range of cells based on the day number.

In the image, days 1 to 10 are shown in a month. E.g. today is 10th of May, so I want to highlight this column (e.g. in bright yellow). When I try to apply conditional formatting I get very inconsistent results for some reason. Sometimes the formatting works spontaneously, but as soon as I change something, the function appears to break.

I’ve tried the following

1. Cell value ; Equals ; DAY(NOW())
2. Formula ; $A10=DAY(NOW())

What does consistently work is when I use something like Cell value ; Equals ; 10. However, this will always be true and will therefore highlight everything all the time.

Even when the cell value contains the formula DAY(NOW()), the conditional formatting is still not working.

Is somehow the abstract value different than the result of the formula?

[EarnestAl]

If the orange stripes in the column are also conditional formatting then the conditional formatting for the day NOW() must be at the top of the list of conditional formats for that range otherwise the range stripes take precedence.

But if they are listed separately in the Manage Conditional Formatting window, that is not in the same Conditional Formatting window then you will have to delete the top condition and rebuild it below (or add a 2nd condition in the Conditional Formatting window)

The below will also work but is harder to manage as you cannot move the order of the conditions

[calvin188]

The formatting that is present in the image was just done by hand, it’s not conditional. But thanks for the insight! The solution provided by PKG works perfectly!

[PKG]

@calvin188

Formula ; $A10=DAY(NOW())

Formula is: A$1=Day(Today())

Untitled 1.ods (19,2 KB)

[calvin188]

It seems that in my particular document it doesn’t work, but it does succeed in your example and when I’ve tried it myself in another document. Is there some setting in my document that has been messed up?

If you have time, please take a look. Thank you in advance!

CF_Daynumbers_Example.ods (25.0 KB)

[PKG]

@calvin188

Is there some setting in my document that has been messed up?

See Conditional formatting of cell with decimal value is evaluated wrong - #7 by PKG