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 (http://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:
Note: For coloring whole column in the table it’s very important to keep the $ character in this order.
If you have table with dates in column A and you want to color rows, just change place of $ character, like this:
If you have a table, where there are only dates, you can write formula without $ character.