Author: Oscar Cronquist Article last updated on January 17, 2018

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:

=SMALL(IF(FREQUENCY(IF(($C$31+ROW($1:$168)/24-1/(24*60)>TRANSPOSE(End))+(($C$31+ROW($1:$168)/24-1/(24*60))<TRANSPOSE(Start)), $C$31+ROW($1:$168)/24-1/(24*60), ""), $C$31+ROW($1:$168)/24-1/(24*60))=ROWS(Start), ($C$31+ROW($1:$168)/24)-1/(24), ""), ROW(A1)) + CTRL + SHIFT + ENTER.

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

(Excel 97-2003  Workbook *.xls)

Functions in this article:

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.

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