How to highlight a specific day in the calendar
In this example, I will use conditional formatting to color weekends in the calendar. Let’s have following calendar for checking attendance.
The first question is how to determine, which day in the week is which date in the calendar. We use function WEEKDAY(). This function has various syntax (all you can see in official manual (https://office.microsoft.com/en-us/excel-help/weekday-HP005209336.aspx). We will use this WEEKDAY(B1,2) … results can be 1 – Monday, 2 – Tuesday, … 6 – Saturday and 7 – Sunday.
Now select calendar, click on the Conditional Formatting menu and choose New Rule…
Choose last item Use a formula to determine which cells to format.
Formula will be:
=OR(WEEKDAY(B$1,2)=6,WEEKDAY(B$1,2)=7)
Note: For coloring whole column in the table it’s very important to keep the $ character in this order.
Example 2
If you have table with dates in column A and you want to color rows, just change place of $ character, like this:
=OR(WEEKDAY($A2,2)=6,WEEKDAY($A2,2)=7)
Example 3
If you have a table, where there are only dates, you can write formula without $ character.
=OR(WEEKDAY(A1,2)=6,WEEKDAY(A1,2)=7)
Some different source told me that this can be done only using VBA macro. Thank you for this simple solution. Now I discovered conditional formatting and I love it.
Firstly I would like to say thumbs up the tutorial is a very helpful one, but I am struggling to highlight public holidays for South Africa, and I also wish to highlight the payday which is on the 25th of every month but if the 25th is on the weekend or holiday, a day before that must be highlighted as a payday. This is my internship task please help I really need to achieve it (highlighting holidays and payday) since I am still building a career. My dates in horizontal format start in column B (cell B6) and end in column AF (cell AF6) Thanks in advance!