How to Highlight Weekend Dates in Excel Using a Formula

Excel is a powerful tool for working with dates and times. You can use Excel to perform various calculations and analysis on date and time data, such as finding the difference between two dates, calculating the number of workdays in a period, or creating a calendar.

One of the common tasks that you may encounter when working with dates in Excel is highlighting weekend dates. Highlighting weekend dates can help you visually distinguish them from weekdays or emphasize their non-working status. For example, you may want to highlight weekend dates when creating a project timeline, a work schedule, or a report.

In this article, you will learn how to highlight weekend dates in Excel using a formula. You will also learn the basic theory behind the formula, the steps to apply the formula, and some alternative approaches to achieve the same result.

To highlight weekend dates in Excel using a formula, you need to use the conditional formatting feature. Conditional formatting allows you to set rules that apply formatting to cells based on their contents. For example, you can set a rule that applies a background color to all cells that contain weekend dates.

The formula that you need to use for highlighting weekend dates is based on two functions: the WEEKDAY function and the OR function.

The WEEKDAY function takes a date and returns a number between 1 and 7 representing the day of the week. By default, the WEEKDAY function returns 1 for Sunday and 7 for Saturday. For example, if you have the date January 1, 2024 in cell A1, the WEEKDAY function will return 1, since it is a Sunday:

=WEEKDAY(A1) // returns 1

The OR function returns TRUE if any of the given arguments evaluate to TRUE, and returns FALSE if all of the arguments evaluate to FALSE. For example, if you have the values 10 and 20 in cells A1 and A2, respectively, the OR function will return TRUE if either of them is greater than 15:

=OR(A1>15,A2>15) // returns TRUE

You can combine the WEEKDAY function and the OR function to test if a date is a weekend date. A date is a weekend date if its weekday number is either 1 or 7. Therefore, you can use the following formula to check if a date in cell A1 is a weekend date:

=OR(WEEKDAY(A1)=1,WEEKDAY(A1)=7) // returns TRUE if A1 is a weekend date

Procedures

To highlight weekend dates in Excel using a formula, you need to follow these steps:

1. Select the range of cells that contain the dates that you want to highlight. For example, if you have the dates in column A from row 2 to row 20, select the range A2:A20.
2. On the Home tab, click on the Conditional Formatting button in the Styles group, and choose New Rule from the drop-down menu.
3. In the New Formatting Rule dialog box, select the option Use a formula to determine which cells to format in the Select a Rule Type section.
4. In the Format values where this formula is true box, enter the formula that checks if a date is a weekend date, using the first cell in your selected range as a reference. For example, if your selected range is A2:A20, enter the formula:

=OR(WEEKDAY(A2)=1,WEEKDAY(A2)=7)

5. Click on the Format button to choose the formatting that you want to apply to the weekend dates. For example, you can choose a fill color, a font color, a border, or a number format. In this example, we will choose a light green fill color.
6. Click OK to close the Format Cells dialog box, and then click OK again to close the New Formatting Rule dialog box. You will see that the cells that contain weekend dates are highlighted with the chosen formatting.

Example

Let’s see an example of how to highlight weekend dates in Excel using a formula. Suppose you have the following data in your worksheet, showing the orders placed with a soft drinks manufacturer on various dates:

You want to highlight the cells in the Order Date column that contain weekend dates. To do this, you can use the following steps:

1. Select the range B2:B11 that contains the dates that you want to highlight.
2. On the Home tab, click on the Conditional Formatting button in the Styles group, and choose New Rule from the drop-down menu.
3. In the New Formatting Rule dialog box, select the option Use a formula to determine which cells to format in the Select a Rule Type section.
4. In the Format values where this formula is true box, enter the formula:

=OR(WEEKDAY(B2)=1,WEEKDAY(B2)=7)

5. Click on the Format button to choose the formatting that you want to apply to the weekend dates. For example, you can choose a light green fill color.
6. Click OK to close the Format Cells dialog box, and then click OK again to close the New Formatting Rule dialog box. You will see that the cells that contain weekend dates are highlighted with the chosen formatting, as shown below:

Alternative Approaches

There are some alternative approaches to highlight weekend dates in Excel using a formula. Here are some of them:

• You can use a different return type for the WEEKDAY function to change the numbering sequence of the days of the week. For example, you can use the return type 3 to specify 0 for Monday, 1 for Tuesday, and so on. In this case, the weekend dates will have the weekday numbers 5 and 6, so you can use the following formula:

=OR(WEEKDAY(B2,3)=5,WEEKDAY(B2,3)=6)

• You can use the NOT function to reverse the logic of the formula. Instead of checking if a date is a weekend date, you can check if a date is not a weekday date. For example, you can use the following formula:

=NOT(AND(WEEKDAY(B2)>1,WEEKDAY(B2)<7))

• You can use the NETWORKDAYS function to count the number of workdays between two dates, excluding weekends and holidays. If the function returns zero, it means that the date is a weekend date or a holiday. For example, you can use the following formula:

=NETWORKDAYS(B2,B2)=0

Note that the NETWORKDAYS function requires you to specify a range of cells that contain the list of holidays, if any. You can also use the NETWORKDAYS.INTL function to customize the definition of weekends and holidays.