Fix Excel Conditional Formatting for Start and End Dates
Question details
Create or fix a conditional-formatting rule that highlights dates falling between the start date and end date in each row.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet uses a date header row and row-specific start and end dates to control conditional formatting.
- Observed behavior
- The end-date condition does not work when the formula references a text row, an incorrect date row, or cells containing text instead of real dates.
Identify the row that contains the actual calendar dates and the columns that contain each row's start and end dates. Check that these cells are stored as dates, not as text that only looks like a date.
Use mixed references for the actual date row and each start/end date
Use this method when dates are in row 6 and each record's start and end dates are in columns B and C.
The date header reference must keep row 6 fixed while allowing the column to change across the applied range. The start and end date references must keep columns B and C fixed while allowing the row to change for each record.
In Excel, select the cells that should be highlighted, such as G8:Z100. Do not include the date header row unless you want it formatted too.
Go to Home > Conditional Formatting > New Rule, then choose Use a formula to determine which cells to format.
Enter =(G$6>=$B8)*(G$6<=$C8). Replace G with the first column of your selected range, 6 with the actual date-header row, and 8 with the first data row.
Select Format, choose the fill, font, or border you want, then click OK. In Home > Conditional Formatting > Manage Rules, confirm that the Applies to range covers the intended date grid.
Convert text dates to real Excel dates before applying the rule
Use this method when the formula is correct but no cells, or only some cells, are highlighted.
Highlight Start-to-End Date Ranges with WPS Spreadsheets
WPS Spreadsheets lets you create formula-based conditional formatting rules to highlight schedule dates between a start date and an end date. Use the same mixed-reference logic to format date grids accurately and manage rules in a familiar spreadsheet interface.
- 1. Select the date range: In WPS Spreadsheets, select the schedule cells you want to format, beginning with the first date column and first record row.
- 2. Create a formula rule: Choose Home > Conditional Formatting > New Rule and select the option to use a formula for determining which cells to format.
- 3. Add the start-to-end formula: Enter a formula such as =(G$6>=$B8)*(G$6<=$C8), adjusting the first column, date-header row, and first record row for your sheet.
- 4. Choose the highlight style: Click Format, choose a fill color or other style, and confirm the rule to apply it to the selected range.
- 5. Verify date values: Make sure the header, start-date, and end-date cells use real date values; use Data tools to convert text dates if comparisons do not work.
Frequently Asked Questions
Why does my Excel conditional formatting rule ignore the end date?
The formula may point to the wrong date row or the end-date cell may contain text. In Home > Conditional Formatting > Manage Rules, confirm the formula uses the actual date header, such as G$6, and a row-relative end date, such as $C8.
What does G$6 mean in a conditional-formatting formula?
G$6 is a mixed reference. The column G changes as the rule evaluates cells across the row, while row 6 remains fixed so every cell is compared with the date in the header row.
How do I apply one date-range rule to multiple rows in Excel?
Select the complete data grid first, such as G8:Z100, then create the rule using the first cell of that selection: =(G$6>=$B8)*(G$6<=$C8). Excel adjusts the relative row references for each row.
How can I tell whether Excel recognizes a value as a real date?
In an empty cell, use =ISNUMBER(reference), such as =ISNUMBER(G6). TRUE means Excel stores the value as a numeric date; FALSE means it is likely text and should be converted through Data > Text to Columns.
