## Find empty hours in a weekly schedule

The image above demonstartes an array formula in cell B34 that extracts empty hours in a weekly calendar. I have created some random time ranges located in an Excel defined table. We are going to use these time ranges to extract empty hours between ranges.

I have reused the same weekly schedule template as in this post: Highlight specific time ranges in a weekly schedule As you can see in the top picture, I have applied conditional formatting to "highlight" the random date/time ranges I created earlier.

You can easily spot empty hours but how do you filter all the empty hours for this week? The following array formula demonstrates how:

**Array formula in B34:**

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

Copy cell B34 and paste it down as far as needed. Cell C31 contains the start search date. In this example 8/1/2010. Simply create link to cell F2 if you want to search from beginning of chosen week.

### Explaining formula in cell B34

#### Step 1 - Create hours in week and compare to time ranges

This part of the formula creates Excel date and time values based on hours, it then compares these values with the time ranges in the Excel defined Table to determine if outside range.

($C$31+ROW($1:$168)/24-1/(24*60)>TRANSPOSE(Table1[End]))+(($C$31+ROW($1:$168)/24-1/(24*60))<TRANSPOSE(Table1[Start]))

returns

{0, 1, 1, 1, 1, 1; very large array ;1, 1, 1, 1, 1, 1}.

#### Step 2 - Filter hours not in time ranges

If the hour value is outside all ranges in Excel defined table the IF function returns the value and if FALSE then return nothing "".

IF(($C$31+ROW($1:$168)/24-1/(24*60)>TRANSPOSE(Table1[End]))+(($C$31+ROW($1:$168)/24-1/(24*60))<TRANSPOSE(Table1[Start])), $C$31+ROW($1:$168)/24-1/(24*60), "")

returns a very large array containing date and time values.

#### Step 3 - Count hour values

The FREQUENCY function calculates the frequency of how many date and time values that are outside the ranges specified in the Excel defined table.

FREQUENCY(IF(($C$31+ROW($1:$168)/24-1/(24*60)>TRANSPOSE(Table1[End]))+(($C$31+ROW($1:$168)/24-1/(24*60))<TRANSPOSE(Table1[Start])), $C$31+ROW($1:$168)/24-1/(24*60), ""), $C$31+ROW($1:$168)/24-1/(24*60))

#### Step 4 - Compare the sum to the number of ranges

If the frequency is equal to the number of ranges in the Excel defined Table then return date and time value.

IF(FREQUENCY(IF(($C$31+ROW($1:$168)/24-1/(24*60)>TRANSPOSE(Table1[End]))+(($C$31+ROW($1:$168)/24-1/(24*60))<TRANSPOSE(Table1[Start])), $C$31+ROW($1:$168)/24-1/(24*60), ""), $C$31+ROW($1:$168)/24-1/(24*60))=ROWS(Table1[End]), ($C$31+ROW($1:$168)/24)-1/(24), "")

#### Step 5 - Extract the k-th smallest number

The SMALL function extracts the k-th smallest number in array based on the number in the second arguemnt. SMALL( array, k). This allows us to return different values in each cell.

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

### Schedule category

In this post I am going to add one more function to the weekly schedule I built in a previous […]

Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of […]

In a previous post I created a simple weekly schedule with dynamic dates, in this post I am going to […]

I would like to share this simple weekly schedule I created. How to use weekly schedule Type any date in cell […]

This template makes it easy for you to create a weekly school schedule, simply enter the time ranges and the […]

This schedule uses the year and month in cell D1 and K1 to highlight activities like vacation specified in the […]

Here is my contribution to all excel calendars out there. My calendar is created in Excel 2007 and uses both […]

The image above demonstrates conditional formatting highlighting hours outside work hours, those cells are filled with grey except weekends. Conditional formatting […]

Sam asks: One more question for the Calendar that you have set up above can we have a excel formula […]

This weekly calendar is easy to customize, you can change calendar settings in sheet "Settings": Start date (preferably a Sunday or […]

The above picture shows you two formulas that extract names (column B) and date ranges (column C and D) based […]

## Functions in this article

More than 1300 Excel formulas

## Excel formula categories

## Excel categories

### One Response to “Find empty hours in a weekly schedule”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

How would you adapt this to make it show the to and from times of the empty time slots, taking you example I would need to get display of -

1st of August 2010

12am until 7 am - Occupied

8am until 10am - Free Time Slot

10am until 11pm - Occupied

This would need to be dynamic as entries were made or appointments cancelled

Cheers

JD