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