Author: Oscar Cronquist Article last updated on December 23, 2018

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, demonstrated in the picture above Here are some random date and time ranges:

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 workaround.

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 file


* You will also get a weekly newsletter, unsubscribe anytime!