In a previous post I created a simple weekly schedule with dynamic dates.

In this post I am going to highlight hours using date and time ranges.

Here are some random ranges:

Here is a picture of the weekly schedule and affected cells highlighted grey.

Try change date in cell F2 and see how any other week has no highlighted cells (hours).

How to highlight cells in a weekly schedule (conditional formatting)

Conditional formatting does not accept cell references outside current worksheet.

But there is a work-around.

Create a named range for each column and use the named ranges in a conditional formatting formula.

Create named ranges

  1. Select sheet "Time ranges"
  2. Select range B3:B5
  3. Type "Start" in name boxand press Enter
  4. Select range C3:C5
  5. Type "End" in name box and press Enter

Create conditional formatting (Excel 2007)

  1. Select sheet "Weekly schedule"
  2. Select cell range C6:I30
  3. Click "Home" tab on the ribbon
  4. Click "Conditional formatting"
  5. Click "New Rule.."
  6. Click "Use a formula to determine which cells to format"
  7. Type in "Format values where this formula is true" window:
    =SUMPRODUCT((C6>=Start)*(C6<End))
  8. Click "Format..." button
  9. Select "Fill" tab
  10. Select a color
  11. Click Ok
  12. Click Ok
  13. Click Ok

Download excel template

Highlight time ranges in a weekly schedule.xls
(Excel 97-2003  Workbook *.xls)

Functions in this article:

SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays