Formatting Issues

How to Display Holiday Dates for the Next Four Weeks in Excel

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to display holidays marked with an 'H' from a Tracker sheet onto a front page for the upcoming four weeks, and apply green formatting to the corresponding columns.

Product: Excel
Device & OS: not provided
Scenario: Tracking and highlighting upcoming holidays dynamically across multiple sheets.
Observed behavior: The goal is to automatically filter dates marked with an 'H' for the next four weeks and apply a green color format to those columns without merging issues.

Before you start

Ensure that your Tracker sheet contains a clear row or column of dates alongside the cells used to mark holidays with an 'H', and unmerge any cells in your target display area to prevent formatting errors.

Solution 1Recommended

Filter Holiday Dates and Apply Conditional Formatting

Use reference formulas and conditional formatting to dynamically display and highlight the next four weeks of holidays.

When linking data from a Tracker sheet to a display sheet, cell merging can interfere with formulas and conditional formatting. It is highly recommended to unmerge cells in the display area before setting up your rules.

Unmerge cells in the display area

Select the target cells on your front page display area, go to the 'Home' tab, and click 'Merge & Center' to unmerge them if they are currently merged.

Reference the Tracker sheet dates

In the front page display area, set up reference formulas to pull dates from the Tracker sheet, filtering for dates that are marked with 'H' and fall within the next four weeks (e.g., using the TODAY() function).

Set up conditional formatting

Select the columns in the front page where the dates are displayed. Navigate to the 'Home' tab, click 'Conditional Formatting', and select 'New Rule'.

Apply a formula-based formatting rule

Choose 'Use a formula to determine which cells to format'. Enter a formula that checks if the holiday marker equals 'H' (e.g., =A$1="H"), click 'Format' to set the fill color to green, and click 'OK' to apply the rule.

Avoid Merged Cells: Merged cells can cause formula references to shift unexpectedly and break conditional formatting rules applied to entire columns.

Efficiently Track Holidays with WPS

Easily Manage Dates and Apply Conditional Formatting in WPS Spreadsheet

WPS Spreadsheet provides powerful reference formulas and intuitive conditional formatting features, making it effortless to track upcoming holidays across different sheets while maintaining full compatibility with Microsoft Excel files.

1. Open your tracker in WPS: Launch WPS Spreadsheet and open your holiday tracker workbook.
2. Link your dates: Link your front page cells to the Tracker sheet dates using standard reference formulas.
3. Access Conditional Formatting: Select the target columns, go to the 'Home' tab, and click 'Conditional Formatting' > 'New Rule'.
4. Apply the green highlight: Choose the formula rule type, input your 'H' marker formula, select a green fill color, and click 'OK'.

Fully compatible with Microsoft Excel (.xlsx) formulas and formatting rules.Intuitive Conditional Formatting manager to easily highlight rows and columns.Lightweight software with fast processing for large tracker sheets.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting not working across different sheets?

Conditional formatting rules generally cannot directly reference other sheets. You must first pull the data (like the 'H' marker) into the current sheet using standard formulas, and then base your conditional formatting rule on those local cells.

How do I filter dates for exactly the next four weeks?

You can use the TODAY() function in your formulas. For example, you can set a condition to only display dates that are greater than or equal to TODAY() and less than or equal to TODAY() + 28.

Can I conditionally format an entire column based on one cell's value?

Yes. When setting up the conditional formatting formula, use an absolute row reference and a relative column reference (e.g., =A$1="H"), then apply the rule to the entire column range. This ensures the formatting applies down the entire column based on the specific row's value.

Document & File Issues