How to Automatically Highlight Weekends and Holidays in Red?
This article explains how to automatically identify dates in Excel and highlight weekends and holidays in red. To detect weekends, we use Conditional Formatting, and for holidays, we specify a list of Japanese holidays in a separate range.
Steps
- Select the range of cells containing dates (e.g., A2:A10).
- Click on the “Home” tab, select “Conditional Formatting”, and choose “New Rule”.
- Under “Select a Rule Type”, choose “Use a formula to determine which cells to format”.
- Enter the following formula to automatically highlight weekends in red.
Formula to Highlight Weekends in Red
Enter the following formula to automatically highlight weekends in red:
=OR(WEEKDAY(A2, 2) > 5)
Explanation of the Formula
- WEEKDAY(A2, 2): Returns the day of the week for the date in A2 (1 = Monday, 7 = Sunday).
- WEEKDAY(A2, 2) > 5: Returns TRUE if the date in A2 is Saturday or Sunday.
- OR(…): Highlights the cell if the condition is met.
Setting Up Holiday Highlighting
To highlight holidays, create a list of holidays in a separate range and apply conditional formatting using that range.
Steps
- Enter the list of Japanese holidays in any range (e.g., C2:C20) on a separate sheet or the same sheet.
- Create a new rule in Conditional Formatting and enter the following formula:
=COUNTIF($C$2:$C$20, A2) > 0
Explanation of the Formula
- COUNTIF($C$2:$C$20, A2): Checks if the date in A2 is included in the holiday list in the range C2:C20.
- COUNTIF(…, A2) > 0: Returns TRUE if the date in A2 is found in the holiday list, applying conditional formatting.
Example
In the table below, column A contains dates, and cells corresponding to weekends and holidays are automatically highlighted in red. The holiday list is specified in another range. You can customize the list to include company holidays or other specific dates.
| A | B | C | |
|---|---|---|---|
| 1 | Date | Day | Holiday List (Partial) |
| 2 | 01/01/2024 | Mon | 01/01/2024 |
| 3 | 06/01/2024 | Sat | 11/02/2024 |
| 4 | 07/01/2024 | Sun | 23/02/2024 |
| 5 | 08/01/2024 | Mon | 20/03/2024 |
| 6 | 13/01/2024 | Sat | |
| 7 | 14/01/2024 | Sun |
Result
- Weekends: Cells are automatically highlighted in red for Saturdays and Sundays.
- Holidays: Cells are automatically highlighted in red if the date matches an entry in the holiday list.
Summary
To automatically highlight weekends and holidays in Excel, use Conditional Formatting with formulas. For weekends, use the WEEKDAY function, and for holidays, specify a list of holidays in a separate range to apply formatting based on the list.