Formatting cell color accordingly to the day of the week.

[Frankxl]

Good afternoon, I an enormous number of cells with dates on it.
How can conditionally format them so I can apply different colors
depending on the day of the week.
example of the dates: 07/11/2022
That one is Monday and I want it to show with blue background.
Conditionally Formatting for Monday is easy to do as it is self
explanatory on the worksheet but in MY case, there is no word Monday
on the cell and I want it to be that way, just the date but blue if is Monday.
Also, to be able to color other days of the week. Is this possible on XL?
Sorry for asking too much but please I do not know how to use VBA or Macros
and prefer not to. Thank.s

[etaf]

you can use a conditional formatting rule
weekday(cell, 1) then Sunday =1 , monday =3 etc

Weekday(cell, 1)=1
colour ?

Weekday(cell, 1)=2
colour blue

etc

Not sure what range you are using

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A sample sheet would help here


Please see the Yellow Banner at the top of the page explaining how to attach a sample spreadsheet

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Here are the instructions, found at the top of the page again

Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
B2:D100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=weekday(B2,1)=2

Format [Number, Font, Border, Fill] Blue
choose the format you would like to apply when the condition is true
OK >> OK

repeat for the other 6 days of week

[Frankxl]

The formula is perfect, but I have some issues with the worksheet, somewhat locked or something.
I don't know if the previous message was sent, no friendly user the forum but here is again.
Looking at posts I saw a formula and a solution to my worksheet called helper column (wonderful tool).
Here is a little piece of my worksheet.
A1 7/1/2022
A2 7/2/2022
so, I put in B1 =TEXT(A1,"dddd")
B2 =TEXT(A2,"dddd") then I colored conditional formatting days of the week as I wanted it.
Q: Could I put conditional formatting in A1 and A2 so they will be the same color of B1 and B2 respectively?
If that is possible then after I will hide B column and keep the A with the dates colored by each B in the right.
Thanks.

[etaf]

you can do that
OR just in a column use
weekday(A1,1)
that will give you numbers from 1 to 7
if you use a $ it fixes the cell
SO
weekday($A1,1)
or
=TEXT($A2,"dddd")

then select A2:A100
and use a conditional formatting

=$B2="monday"
and colour
OR
if using weekday
=$B2=1

i have only added 2 rules just to give an example

dont really understand why you cant just select A and use the formula in conditional formatting

can you attach the spreadsheet - following the rules in yellow banner

[Frankxl]

Correct but if some of the cells in B are empty and they are colored as I wish, could I color A the same?
This case will be simple if we forget about the dates and day because not all cells in column A are filled, neither all in column B.
If B10 is empty and colored RED.
Can I automatically color A10 RED too? That is using Conditional Formatting.

[etaf]

no,
if you have a cell that is manually filled in a colour , then to make another cell the same colour would need VBA to do - and i do not provide VBA solutions here

conditional formatting using formula and conditions to apply colours

if there is a rule to make B10 RED in conditional formatting - like
conditional formatting rule
=B10=""
then you can apply to cell A10 - as explained before

select A10
conditional format rule
=$B10=""
that will colour A10