Forum Discussion
Excel Yearly calender conditional formatting
Hey! I am trying to make a yearly calender and automatticly format certaint days of the week.
This is what i have so far!
I have a dropdown list which allows me to pick the year. And i've also got a month indicator which is made purely for formatting things.
For instance Januari is calculated like this:
The calender days themself all are calculated like this:
=TEXT(YearNumber+1, "ddd")
All the way till +6.
Now i've coloured Saturday and Sunday as u see. However it is based on YearNumber which is a variable that changes value when i change years. So u will see if i make it to 2023
As u can see this is not the wished outcome. Any way to take the value "ddd" Such as Sat, Sun.
Its not the actual value but if i'd be able to use that then maybe i could figure it all out.
Select the cells that you want to format conditionally.
On the Home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Lave the first drop down set to 'Cell Value'.
Select 'equal to' from the second drop down.
Enter ="Sat" in the box next to it.
Click Format...
Activate the Fill tab.
Select green.
Click OK, then click OK again.
Repeat the above steps, but with ="Sun" instead of ="Sat".
31 Replies
Select the cells that you want to format conditionally.
On the Home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Lave the first drop down set to 'Cell Value'.
Select 'equal to' from the second drop down.
Enter ="Sat" in the box next to it.
Click Format...
Activate the Fill tab.
Select green.
Click OK, then click OK again.
Repeat the above steps, but with ="Sun" instead of ="Sat".
Hey! I thought i tried this but i think i used formula instead. Somehow i messed up. This works thank you. However i also want the color next to that cell coloured do you know how i could do that? Color 1 cell to the right of the valued one. See picture for example. I want it to automatticly do what i did manually. It is just red so u can see it easily but it will be the same color as Sat/Sun
Select the entire range you want to format conditionally. It's probably E5:AB35.
E5 should be the active cell in the selection.
Delete any existing conditional formatting rules.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=(OFFSET(E5,0,-ISEVEN(COLUMN(E5)))="Sat")+(OFFSET(E5,0,-ISEVEN(COLUMN(E5)))="Sun")
Click Format... and proceed as usual.
