In this post, I have created som random time ranges. We are going to use these time ranges to extract empty hours between ranges.

Random date/time ranges:

I have reused the same weekly schedule as in this post: Highlight specific time ranges in a weekly schedule in excel

As you can see in the picture below, I have used conditional formatting to "highlight" the random date/time ranges I created earlier in this post.

The  empty hours list is bigger but I had to crop the picture somewhere.

You can easily spot empty hours but how do you filter all the empty hours for this week?

Array formula in B34:

Copy cell B34 and paste it down as far as needed.

Cell C31 contains the start search date. In this example 2010-08-01.

Named ranges

Start (B3:B5)
End (C3:C5)

Download excel template

Find-empty-hours-in-a-weekly-schedule.xls
(Excel 97-2003  Workbook *.xls)

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

SMALL(array,k) returns the k-th smallest row number in this data set.

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

ROW(reference) returns the rownumber of a reference

FREQUENCY(data_array, bins_array)
Calculates how often values occur within a range of values and then returns a vertical array of numbers having one more element than Bins_array.

ROWS(array) returns the number of rows in a reference or an array

DATE(year,month,day) returns the number that represents the datein Microsoft Office Excel date-time code

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