## 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))

Populate cells dynamically in a weekly schedule

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 […]

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

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

Highlight specific time ranges in a weekly schedule

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

I have built a sheet to track time at work. It is very simple, there are 13 sheets, one for each […]

Calculate time between time zones

The worksheet below lets you enter cities and their time difference. Excel calculates the corresponding local times in E5:E8. Press F9 to […]

Count entries based on date and time

Question: My issue is that I get the date in this format: 7/23/2011 7:00:00 AM I am trying to count […]

Column C contains both date and time, to calculate the average of only time we need to extract the hours, minutes […]

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