Your Free Project Scheduling and Gantt Chart Tool Using Microsoft Excel

Highlighting Weekends

To make the weekends more outstanding in the Calendar, we can use a simple formula in Conditional Formatting to set the background and text color of the dates that falls on weekends.

	Diagram showing the weekends being highlighted with pink background and red text.
	Using Conditional Formatting formula to set the background and text color of the cells.

We use the following formula to identify weekends.

 =OR(IF(TEXT(WEEKDAY(N$3),"ddd")="Sat",1),IF(TEXT(WEEKDAY(N$3),"ddd")="Sun",1))

where N$3 is the cell that we are testing.

WEEKDAY(Value) returns a number that represents the day of the week.

TEXT(Value,"ddd") converts the number to a 3 character day representative.

We use OR(IF(....)) to test if the returned date is either a "Sat" or "Sun" if the condition is true, the test will return a "1" to the Conditional Formatting.

Next: Conditional Formatting >>