How to Automatically Highlight Weekends and Holidays in Excel

You need to make weekend and holiday dates stand out in your project schedules or calendars. Excel can apply color formatting to cells based on their date values. This article explains how to use Conditional Formatting to automatically highlight these dates.

Using Conditional Formatting for Date-Based Rules

Excel’s Conditional Formatting feature changes a cell’s appearance based on rules you set. For dates, you create formulas that evaluate to TRUE or FALSE. When the formula is TRUE for a cell, your chosen formatting style applies. You need a range of cells containing valid dates. Excel will not highlight cells containing text or errors.

Prerequisites for Date Highlighting

Your data must be in a proper Excel date format. Select your date cells and check the Number Format in the Home tab. It should show a date category like *3/14/2012*. Also, decide where you will list your holiday dates. It is best to put them in a separate column on the same worksheet.

Steps to Highlight Weekend Dates

Follow these steps to color all Saturdays and Sundays in your selected date range.

1. Select your date range
   Click and drag to select the cells containing the dates you want to format.
2. Open the Conditional Formatting menu
   Go to the Home tab on the ribbon. Click Conditional Formatting in the Styles group. Choose New Rule from the dropdown list.
3. Select the rule type
   In the New Formatting Rule dialog box, select the last option: ‘Use a formula to determine which cells to format’.
4. Enter the weekend formula
   In the formula input box, type: =WEEKDAY($A2,2)>5. Replace ‘$A2’ with the address of the top-left cell in your selected range. The ‘2’ argument makes Monday=1 and Sunday=7.
5. Set the format
   Click the Format button. Choose a Fill color, like light gray or yellow. Click OK to close the Format Cells window.
6. Apply the rule
   Click OK in the New Formatting Rule dialog box. Your weekend dates will now be highlighted.

Steps to Highlight Holiday Dates

You can add a second rule to highlight dates from a separate list you maintain.

1. Create a holiday list
   In a separate column, enter all your holiday dates. Give this range a name like ‘Holidays’ by selecting it and typing the name in the Name Box.
2. Select your main date range again
   Click on your original schedule or calendar date range.
3. Create a new rule
   Go to Home > Conditional Formatting > New Rule. Again, choose ‘Use a formula’.
4. Enter the holiday formula
   Type: =NOT(ISERROR(MATCH($A2, Holidays, 0))). Replace ‘$A2’ with your top cell and ‘Holidays’ with your named range or cell reference.
5. Set a distinct format
   Click Format and choose a different Fill color, like light red. Click OK twice to apply the rule.

Common Mistakes and Formatting Limits

Avoid these errors to ensure your highlighting works correctly every time.

Conditional Formatting Not Applying to New Rows

If you insert new rows, the formatting may not copy down. To fix this, apply the rules to a full table column instead of a static range. Convert your data to an Excel Table with Ctrl+T. The formatting will then apply to all new rows automatically.

Formula Uses Wrong Cell Reference Type

Using a relative reference like A2 without the column lock ($) can cause incorrect highlighting. For a vertical date column, use $A2. This locks the column but allows the row to change as the rule is applied down the column.

Holiday List Contains Invalid Dates

If your holiday list has text entries or wrong formats, the MATCH function will fail. Ensure all cells in your holiday list are valid Excel dates. Use the DATE function like =DATE(2024,12,25) for accuracy.

Weekend vs. Holiday Highlighting Methods

Item	Highlighting Weekends	Highlighting Holidays
Core Function	WEEKDAY	MATCH
Data Needed	Single date column	Date column plus a separate holiday list
Rule Management	One static formula	Formula updates when holiday list changes
Best for	Standard calendars	Company-specific or regional schedules
Format Overlap	Can be combined with holiday rule	Should use a distinct color from weekend rule

You can now visually separate weekends and company holidays in your spreadsheets. Try applying these rules to a Gantt chart template for better project tracking. Use the Manage Rules dialog to edit the formulas if your date range moves.