Conditional Format - Highlight Cell if Not Sunday

[SaraWitch]

Hello peeps.

I have Column R formatted to ddd, dd/dd/dddd. If the date entered is not a Sunday, I want to highlight it red. I have tried CFs:
=ISERROR(FIND("Sun*",$R3)) (Fill red)

And:
Condition 1: =ISBLANK($R3)=TRUE (No format Set)
Condition 2: = ISNUMBER(SEARCH("Sun*",$R3))=FALSE (Fill red)

Both are highlighting all the cells (which are either blank or have a Sunday date (e.g., Sun, 27/06/2021)).

Any help would be appreciated...

Ta muchly!

 

[BenMcBen]

=text(yourcell,"DDD")<>"Sun"

 

[SaraWitch]

That's grand; thank you, @BenMcBen! And how do I not highlight if blank cell*?

*Don't worry, I figured it out by adding another CF:
ISBLANK($R3) ...then ticking 'Stop If True'.

Thank you for your speedy fix, @BenMcBen!

 

[BenMcBen]

Use the "AND()" function to add an extra check - eg (several ways you could skin this cat):

=and(text(yourcell,"DDD")<>"Sun",NOT(ISBLANK(yourcell)))

There's a lot of variations to the exact formula you could use above (e.g. using the WEEKDAY() function), you could also use separate CF rules with stops etc...

 

[Fluff]

No need to add another rule, you can use

=and(text($R3,"ddd")<>"Sun",$R3<>"")

 

[BenMcBen]

e.g. =WEEKDAY(G11)<>7

 

[SaraWitch]

Perfect; thanks, both.

I've actually ended up using data validation to not allow dates other than a Sunday!

This is all useful to my Excel knowledge though and I can use this elsewhere, so thank you!

HNY! ?

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[SaraWitch]

Sorry, guys; I have another element I need to add!

If I want this to apply only if "EBC" or "LDC" in Column E, how would this look?

 

[Fluff]

How about

=AND(TEXT($R3,"ddd")<>"Sun",$R3<>"",OR($E3="EBC",$E3="LDC"))