Conditional Formatting For WEEKDAY()

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Will Simmons - 18 Jun 2005 19:34 GMT

Running Excel 98 under OS 9.2.2 on a beige G3 with 352 MB Built-in memory.

I want to highlight the Saturdays and Sundays in a column of cells
containing dates named (creatively) "Dates" and, based on messages in the
Group's Google Archive, I've tried "Formula is WEEKDAY(Dates,1) = 1 as
Condition 1 (with "=7" at the end in Condition 2).

Excel accepts my input without an error message of any sort, but nothing
happens. My specified highlight formatting does not appear.

What am I doing wrong ? TIA for any help.

-- Will --

Reply to this Message

JE McGimpsey - 18 Jun 2005 19:49 GMT

Using the entire column in your formula won't work - it will create an
error, so the CF isn't applied. Errors in CF evaluations don't cause any
error messages. When you don't see what you expect, try entering the
formula in the worksheet to see if it results in a TRUE/FALSE (or
non-zero/zero) answer.

Select the column (say, column A). With cell A1 active, enter

CF1: Formula is =WEEKDAY(A1,3)>4
Format1: <your color>

XL will adjust the cell references for you in the other cells. If A2
were the active cell, you'd use A2 instead.

Note that using the second argument for WEEKDAY requires only one
condition.

> Running Excel 98 under OS 9.2.2 on a beige G3 with 352 MB Built-in memory.
>
[quoted text clipped - 9 lines]
>
> -- Will --

Reply to this Message

Will Simmons - 29 Jun 2005 22:05 GMT

>Using the entire column in your formula won't work - it will create an
>error, so the CF isn't applied. Errors in CF evaluations don't cause any
>error messages. When you don't see what you expect, try entering the
>formula in the worksheet to see if it results in a TRUE/FALSE (or
>non-zero/zero) answer.

You are (as always) correct, but I didn't get that out of the online Help.
And, not having this tutelage, I think I actually have conditionally
formatted a named range by highlighting it and just inserting my formula.
IS WEEKDAY a special a case of some kind ?

>CF1: Formula is =WEEKDAY(A1,3)>4
>Format1: <your color>
>XL will adjust the cell references for you in the other cells. If A2
>were the active cell, you'd use A2 instead.

Perfect -- as always. (Envious sigh supplied by the undersigned.)

>Note that using the second argument for WEEKDAY requires only one
>condition.

Oh, of course. Obvious. I mean, if one is clever enough to note that the
"3" signal makes Monday = 0, so the weekend is >4, it's open-and shut. (I
shall sign off before sobbing.)

-- Will --

Reply to this Message

Mac Forum / Applications / Excel / June 2005

Conditional Formatting For WEEKDAY()