When you have a list of dates in Excel it can be useful to know which of those dates are weekends. You can automate a format for weekends using a conditional format.

In the image below we have a list of dates for May. The list is driven by the yellow input cell in cell A1.

We want to create a conditional format that will automatically update the dates when you change the date in cell A1.

To apply the conditional format first select the range A2:A32.

Then click the Conditional Formatting icon drop down in the middle of the Home ribbon.

Select New Rule.

In the dialog that opens select the last option in the top section – Use a formula to … in mage below.

In the formula box type.

=weekday(A2,2)>5

Click the Format button and click the Fill tab and choose a colour – I used grey. Click OK and OK again to apply the format.

The result is shown below.

The formula you create in the formula box must return TRUE to apply the format. This means it needs to be what is called a logical test, which typically appears at the start of an IF function.

In the formula above we are using the ,2 option in the WEEKDAY function. This numbers the weekdays from 1 to 7 from Monday to Sunday. This means that the weekend is any number above the number 5.

It is important to note the use of the reference in the formula. I have used a relative reference to reference cell A2. This means that as the conditional format is applied to the cells beneath it will use the correct cell. The relative reference refers to the top left cell in the range selected.

When creating the formula if you use the mouse the select the cell Excel will insert a fixed/absolute reference with both dollar signs in the reference. You need to remove those dollar signs and ensure that the reference is a relative reference which means there are no dollar signs used.

To confirm that the weekends are highlighted correctly we can add a formula on the right-hand side to show what day of the week it is. In cell B2 enter.

=TEXT(A2,"ddd")

This displays the abbreviated day of the week. You can copy it down the range.

Changing cell A1, updates the list and the formats.