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 through email

Enter your email address to receive the workbook. Note, an email will actually be sent to you.
* You will also get a weekly newsletter, unsubscribe anytime!