Visually identify weekend dates using Excel's conditional formatting feature.
Building the sheet from scratch is simple. Using the above figure as a guide, enter the appropriate formatting and labels to create a sheet similar to the one show below.
Next, enter the following formula in cell C6 and copy to the remaining calendar cells in row 6:
This function builds a serial date value by combining the year value in C3, the month value in C2, and the day value in C5. The first two references are absolute and the last is relative, so the formula refers to the values in row five once you copy it D6:AG6. At this point, row six contains repeating values, values 1 through 7. These values represent the day of the week with Sunday being the first day of the week and Saturday being the last day of the week: Sunday, 1; Monday, 2; Tuesday, 3; Wednesday, 4; Thursday, 5; Friday, 6; and Saturday, 7.
To display the names instead, you can apply a custom format as follows:
- Select C6:AG6.
- Click the Number group (on the Home tab) dialog launcher. In Excel 2003, right-click the selection and choose Format Cells.
- In the Category list, select Custom.
- In the Type field, enter ddd.
- Click OK.
Now, it's time to apply the conditional formatting rule as follows:
- Select C5:AG12 (your calendar may contain more rows of course).
- On the Home tab, click Conditional Formatting. In Excel 2003, choose Conditional Formatting from the Format menu.
- Choose New Rule. In Excel 2003, choose Formula Is and skip to step 5.
- In the resulting dialog, select the last rule type: Use A Formula To Determine Which Cells To Format.
- In the Rule Description control, enter the following formula:
- Click Format.
- Click the Fill tab, select a color (I selected tan), and click OK. In Excel 2003, click the Patterns tab.
- Click OK to return to the sheet.
Be careful with that function. You'll notice that I used the value 2 as the WEEKDAY() function's second argument. That's important. Doing so uses Monday as the first day of the week, instead of Sunday - adjusting the values accordingly. This simple step simplifies the conditional formatting formula. The >5 component identifies the values 6 and 7 - which in this case are Saturday and Sunday. If you retain the default, the first day of the week is Sunday and this formula won't work.
Update the month and year values in C2 and C3, respectively, as needed. The conditional formatting will accommodate the weekends accordingly.
You don't have to use a grid-type calendar to use this conditional formatting technique. Below, the following conditional formatting formula highlights weekend dates in a single column: