Formatting Issues

How to Create an Excel Coverage Table with Conditional Formatting

Maira Mehtab Sep 22, 2026 868 views

Question details

The user wants to create a coverage grid in Excel to compare time intervals against hourly or half-hourly headers and automatically highlight periods when coverage is available using conditional formatting.

Product: Excel
Device & OS: not provided
Scenario: Creating a visual schedule or coverage table where specific time blocks are colored dynamically based on a text string containing start and end times.
Observed behavior: A highlighted visual grid that corresponds to start and end times provided in a specific text format (e.g., 'start - end').

Before you start

Ensure your time interval data is consistently formatted (e.g., '08:00 - 12:00') and that your column headers are valid time values so the conditional formatting formula can read them properly.

Solution 1Recommended

Use a Formula with Conditional Formatting

Apply a custom formula using LET, TIMEVALUE, and TEXTSPLIT to evaluate whether a time header falls within the specified start and end times.

This method extracts the start and end times from a text string and checks if the column header falls within that range. It requires Excel 365 or a newer version supporting functions like LET and TEXTSPLIT.

Set up your headers and intervals

Enter your time interval in cell B2 (e.g., '09:00 - 17:00') and place your sequential time headers starting in cell C1.

Open Conditional Formatting

Select the time grid cells where you want the highlighting to appear. Navigate to the Home tab, click on Conditional Formatting, and select New Rule.

Enter the custom formula

Choose the option 'Use a formula to determine which cells to format'. Enter the following formula: =LET(times,TIMEVALUE(TEXTSPLIT($B2," - ")),AND(C$1>=INDEX(times,1),C$1<INDEX(times,2)))

Apply formatting color

Click Format, choose a fill color to highlight the coverage period, click OK, and ensure the rule applies to your entire grid range.

Version Compatibility: The LET and TEXTSPLIT functions are available in modern spreadsheet software. If you are using an older version, you will need to separate your start and end times into two distinct columns instead.

Fast and Easy Spreadsheets

Create Coverage Tables Easily in WPS Spreadsheet

WPS Spreadsheet offers powerful conditional formatting tools and full compatibility with advanced array formulas, making it simple to build dynamic schedules, coverage grids, and automated trackers.

1. Open your schedule: Launch WPS Spreadsheet and open or create your coverage table document.
2. Access Conditional Formatting: Highlight your grid cells, navigate to the Home tab, and click on Conditional Formatting to create a new rule.
3. Apply your formula: Select the custom formula option, input your time comparison logic, apply a distinctive fill color, and click OK.

100% compatible with Microsoft Excel formats (.xlsx)Supports advanced array functions and complex conditional logicFree, lightweight, and fast to launchBuilt-in customizable templates for easy scheduling

microsoft office alternative - wps office

Frequently Asked Questions

Why isn't my conditional formatting formula highlighting the correct cells?

This usually happens if absolute and relative references are incorrect. Ensure you lock the column for the time interval (e.g., $B2) and lock the row for the time headers (e.g., C$1).

Can I separate start and end times into two columns instead of using a text string?

Yes. If your start time is in column B and end time is in column C, with headers starting in D1, the formula simplifies to =AND(D$1>=$B2, D$1<$C2). This also eliminates the need for the TEXTSPLIT function.

How do I format the grid for half-hourly intervals?

Ensure your column headers (C1, D1, E1, etc.) increment by exactly 30 minutes (e.g., 08:00, 08:30, 09:00). The same conditional formatting formula will automatically evaluate each half-hour column against the interval limits.

Document & File Issues