Example Formula for Highlighting Weekends by Date in Excel

When working with schedules and calendars, it's often useful to highlight weekend dates (Saturdays and Sundays) with color. A conditional formatting formula allows you to highlight all weekend dates in a list or calendar in Excel.



How to Highlight Only Weekend Dates by Date in Excel

weekends

Below is an example of automatically highlighting weekend dates with color:

highlight weekend dates with color.

To create such a rule for conditional formatting, follow these step-by-step instructions:

  1. Select the source range of cells (in this example, A2:A17) and choose the tool: "HOME" - "Conditional Formatting" - "Create Rule." The following window will appear:
    2. Create a rule.
  2. In the window that appears, select the option: "Use a formula to determine which cells to format." This gives us the ability to enter a formula to define custom rules and conditions for coloring cells in the selected source range.
  3. In the formula input field, enter the logical formula shown below. Note that only relative cell references are used in the formula. To determine the day of the week for a date in a cell, the WEEKDAY function is used. If the function returns the value 6 or 7, it indicates that the date in the current cell (e.g., A4) is a weekend. The final result of the formula will return TRUE, and the conditional formatting will be applied to that cell:

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

  4. Click the "Format" button, and a familiar window will appear for setting the display style of the cell and its value – "Format Cells," as shown below. In this window, you can specify colors for the fill, fonts, and borders of the cell, along with other formatting properties. After making your selections, confirm them by clicking OK on all open windows.
format button.

The table of weekends for all months throughout the year in Excel:

holidays

A basic interactive calendar using conditional formatting to highlight dates based on a condition:

weekends

Download Example Formula for Highlighting Dates by Color in Excel download file

As a result, only the weekend dates in the date column are highlighted in color.