Conditional Formatting Based on Value of Adjacent Cell

[f252863]

Hi,

I see the solution on the boards but it doesn't work for me.

Cells:

B1 =now()
B2 =weekday(b1)
C2 Conditional formatting =b2="Monday"

Cell B2 is custom formatted "dddd" so it shows the day.

If I type "Monday" into cell B2 it works but if I use "weekday(b1)" it
doesn't even though "Monday" is in the cell. How do I get conditional
formatting to recognize the value Monday as opposed to being confused
by the formula?

 

[Bob Phillips]

It might look like Monday due to its format, bur Now() is a number. You
could use

TEXT(B1,"dddd")

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Stephen]

Hi,

I see the solution on the boards but it doesn't work for me.

Cells:

B1 =now()
B2 =weekday(b1)
C2 Conditional formatting =b2="Monday"

Cell B2 is custom formatted "dddd" so it shows the day.

If I type "Monday" into cell B2 it works but if I use "weekday(b1)" it
doesn't even though "Monday" is in the cell. How do I get conditional
formatting to recognize the value Monday as opposed to being confused
by the formula?

Use this conditional format formula:
=B2=2

The WEEKDAY function returns a number between 1 and 7.
Therefore the underlying value in cell B2 is a number between 1 and 7.
Formatting doesn't change this value - it merely changes how it is
displayed.
Any formula using B2 uses the underlying value.

 

[BoniM]

The cell is formatted to display Monday, but the actual value in the cell is 2.
Change the conditional formatting to =b2=2.