Microsoft

Use Excel's conditional formatting to highlight weekends

Visually identify weekend dates using Excel's conditional formatting feature.

iStock_TGIF.jpg
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.
 

2013191.JPG

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.

2013192.JPG

Next, enter the following formula in cell C6 and copy to the remaining calendar cells in row 6:

=WEEKDAY(DATE($C$3,$C$2,C5))

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.

2013193.JPG

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.

2013194.JPG

Conditional formatting

Now, it's time to apply the conditional formatting rule as follows:

  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 Format.
  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 tab.
  9. Click OK to return to the sheet.

2013195.JPG

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.

2013196.JPG

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:

=WEEKDAY(A2,2)>5

2103197.JPG

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

6 comments
wordsmart
wordsmart

I kinda like DBlayney's method but I would name the seed cell something like StartDate.  But as both Susan and DB say, there's always more than one way to do everything in Excel.

DBlayney
DBlayney

Good tip,as ever. The WEEKDAY function can be very useful.

If it were me, I would have done things differently - not better, just different. I would have put the full date in a cell at the top, let's ay in A1 (hidden if that's how it should be) and then derive everything else from there: C2 and C3 would be filled using MONTH(A1) and YEAR(A1). C5 would be a straight copy using =A1 and then D6 across to AG6 would be created by adding 1 to each cell across the sheet. Row 6 would be derived directly from row 5. Custom formats for rows 5 and 6 would be "d" and "ddd" respectively. (There is no real benefit in using the WEEKDAY() function to calculate a day number and then reformatting it as a day name).

I would probably use a formula like =if(AD5>=EOMONTH($C5,0),"",ad5+1) in columns AE to AG of rows 5 and 6 to suppress superfluous days 29, 30 and 31 when not needed.

The conditional formatting formula then becomes much easier as =WEEKDAY(C$5,2)>5.

Just my preference - more than one way of skinning a cat!

Mark W. Kaelin
Mark W. Kaelin moderator

Susan has been showing us several good uses for conditional formatting - do you use conditional formatting in an inventive way? We'd like to hear about it.

ssharkins
ssharkins

@DBlayney There's always more than one way to do something in Excel -- knowing all your options is what makes you efficient!

DBlayney
DBlayney

This is not "inventive" but it might be useful. When I first started using Excel 2007, I had a big problem with a worksheet where I was doing a lot of copyiing and pasting. Everything went slower and slower until I realised that pasting cells with conditional formats built up huge lists of formatting instructions, most of which were the same. Deleting all the conditional formats and completely reformatting the sheet from time to time solved the problem fully. Maybe this was fixed in a service pack; I have not seen the problem in Excel 2010 but I have developed a habit of renewing conditonal formats as a matter of course.

Editor's Picks