If you’re like me, weekends are important to you so easily
identifying them is important too. Fortunately, by combining a date function
and a conditional formatting rule, you can dynamically highlight weekends in a
simple calendar sheet. The calendar shown below isn’t a comprehensive calendar
– it won’t do everything. For instance, the last three columns (29, 30, and 31)
won’t alert you when they’re not appropriate for the current month. You could
add that functionality, but I haven’t added it to this example.

An example Excel worksheet is provided for your convenience as a free download.

Build it

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:

  1. Select C6:AG6.
  2. Click the Number group (on
    the Home tab) dialog launcher. In Excel 2003, right-click the selection
    and choose Format Cells.
  3. In the Category list,
    select Custom.
  4. In the Type field, enter ddd.
  5. Click OK.

Conditional formatting

Now, it’s time to apply the conditional formatting rule as

  1. Select C5:AG12 (your
    calendar may contain more rows of course).
  2. On the Home tab, click
    Conditional Formatting. In Excel 2003, choose Conditional Formatting from
    the Format menu.
  3. Choose New Rule. In Excel
    2003, choose Formula Is and skip to step 5.
  4. In the resulting dialog,
    select the last rule type: Use A Formula To Determine Which Cells To
  5. In the Rule Description
    control, enter the following formula:
  6. =WEEKDAY(DATE($C$3,$C$2,C$5),2)>5
  7. Click Format.
  8. Click the Fill tab, select
    a color (I selected tan), and click OK. In Excel 2003, click the Patterns
  9. Click OK to return to the

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

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: