Find empty hours in a weekly schedule

Author: Oscar Cronquist Article last updated on January 30, 2023

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:

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

Excel 365 dynamic array formula in cell B34:

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

How to enter an array formula

To enter an array formula, type the formula in a cell.
Press and hold CTRL + SHIFT simultaneously.
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.

How to copy the array formula

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

How to search in the chosen week

Cell C31 contains the start search date. In this example 8/1/2010. Simply create a cell reference to cell F2 if you want to search from the beginning of the chosen week.

Explaining the array formula in cell B34

Excel dates are whole numbers in Excel 1/1/1900 is 1 and 1/2/1900 is 2, and so on.

Hours are fractions, 1/24 is 1:00 AM, 12/24 is 12:00 PM, and so on.

Step 1 - Create a sequence of numbers from 1 to 168

There are 24 hours in one day and seven days in one week. 24*7 = 168 hours in one week.

ROW($1:$168)

returns

{1; 2; 3; ... ; 167; 168}

Step 2 - Divide numbers by 24

The division character lets you divide numbers in an Excel formula.

ROW($1:$168)/24

becomes

{1; 2; 3; ... ; 167; 168}/24

returns

{0.0416666666666667; 0.0833333333333333; 0.125; 0.166666666666667; ... ; 6.95833333333333; 7}.

Step 3 - Add sequence to search date

The plus sign lets you add numbers in an Excel formula.

$C$31+ROW($1:$168)/24

becomes

40391+{0.0416666666666667; 0.0833333333333333; 0.125; 0.166666666666667; ... ; 6.95833333333333; 7}

and returns

{40391.0416666667;40391.0833333333;40391.125; ... ;40397.9583333333;40398}

Step 4 - Multiply 24 by 60

The asterisk lets you multiply numbers in an Excel formula. 24 hours multiplied by 60 minutes equals 1440 minutes in one day.

24*60

retruns

1440.

Step 5 - Divide one with 1440

1/(24*60)

becomes

1/1440

and returns

0.000694444444444444

Step 6 - Subtract the array with one minute in Excel time

$C$31+ROW($1:$168)/24-1/(24*60)

becomes

{40391.0416666667;40391.0833333333;40391.125; ... ;40397.9583333333;40398} - 0.000694444444444444

and returns

{40391.0409722222; 40391.0826388889; 40391.1243055556; ... ;40397.9576388889; 40397.9993055556}

Step 7 - Check if date and time values are larger than END values

The TRANSPOSE function converts a vertical range to a horizontal range, or vice versa.

Function syntax: TRANSPOSE(array)

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

becomes

{40391.0409722222; 40391.0826388889; 40391.1243055556; ... ;40397.9576388889; 40397.9993055556}>TRANSPOSE({40391.3333333333; 40392.4583333333; 40393.6666666667; 40393.25; 40394.4166666667; 40397.9583333333})

becomes

{40391.0409722222; 40391.0826388889; 40391.1243055556; ... ;40397.9576388889; 40397.9993055556}>{40391.3333333333, 40392.4583333333, 40393.6666666667, 40393.25, 40394.4166666667, 40397.9583333333})

and returns

{FALSE, FALSE, FALSE, ... , TRUE, TRUE}

Step 8 - Check if date and time values are smaller than the START values

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.

The parentheses lets you control the order of operation.

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

returns

{FALSE, TRUE, TRUE, ... , FALSE}.

Step 9 - 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 returns 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), "")

becomes

IF({0,1,1, ... ,1,1}, {40391.0409722222; 40391.0826388889; 40391.1243055556; ... ;40397.9576388889; 40397.9993055556}, "")

and returns

{""; 40391.0826388889; 40391.1243055556; ... ;40397.9576388889; 40397.9993055556}

Step 10 - Count hour values

The FREQUENCY function calculates the frequency of how many date and time values 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))

becomes

FREQUENCY{""; 40391.0826388889; 40391.1243055556; ... ;40397.9576388889; 40397.9993055556}, $C$31+ROW($1:$168)/24-1/(24*60))

becomes

FREQUENCY{""; 40391.0826388889; 40391.1243055556; ... ;40397.9576388889; 40397.9993055556}, {40391.0409722222; 40391.0826388889; 40391.1243055556; ... ;40397.9576388889; 40397.9993055556})

and returns

{5; 5; 5; ... ; 6; 0}

Step 11 - 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 the 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), "")

becomes

IF({5; 5; 5; ... ; 6; 0}=ROWS(Table1[End]), ($C$31+ROW($1:$168)/24)-1/(24), "")

becomes

IF({5; 5; 5; ... ; 6; 0}=6, {40391; 40391.0416666667; 40391.0833333333; ... ; 40397.9166666667; 40397.9583333333}, "")

and returns

{""; ""; ""; ""; ""; ""; ""; ""; 40391.3333333333; 40391.375; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; 40392.4583333333; 40392.5; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; 40393.25; ""; ""; ""; ""; ""; ""; ""; ""; ""; 40393.6666666667; 40393.7083333333; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; 40394.4166666667; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; 40397.9583333333; ""}

Step 12 - 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))

becomes

SMALL({""; ""; ""; ""; ""; ""; ""; ""; 40391.3333333333; 40391.375; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; 40392.4583333333; 40392.5; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; 40393.25; ""; ""; ""; ""; ""; ""; ""; ""; ""; 40393.6666666667; 40393.7083333333; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; 40394.4166666667; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; 40397.9583333333; ""}, ROW(A1))

becomes

and returns

40391.3333333333 (8/1/2010 8:00:00 AM)

Get the Excel file

Find-empty-hours-in-a-weekly-schedule.xlsx

2. Highlight non-empty hours in a weekly schedule

Highlight nonempty hours in a schedule

The image above shows a weekly schedule, the grey cells are occupied hours. The white cells are empty blank hours.

Conditional formatting formula applied to cell range C6:I29:

=SUMPRODUCT((C6>=INDIRECT("Table1[Start]"))*(C6<INDIRECT("Table1[End]")))

Populated ranges in a weekly schedule

3.1 Explaining CF formula

Step 1 - Create a structured reference to an Excel Table

Table1[Start]

Step 2 - Workaround

The INDIRECT function returns the cell reference based on a text string and shows the content of that cell reference.

Function syntax: INDIRECT(ref_text, [a1])

INDIRECT("Table1[Start]")

Step 3 - Compare

C6>=INDIRECT("Table1[Start]"))

Step 4 - AND logic

The asterisk character lets you multiply numbers and boolean values in an Excel formula.

(C6>=INDIRECT("Table1[Start]"))*(C6<INDIRECT("Table1[End]"))

Step 5 - Add numbers and return total

The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication.

Function syntax: SUMPRODUCT(array1, [array2], ...)

SUMPRODUCT((C6>=INDIRECT("Table1[Start]"))*(C6<INDIRECT("Table1[End]")))

3.2 How to create Conditional Formatting

Highlight nonempty hours in a schedule

Select cell range C6:I29.
Go to tab "Home" on the ribbon.
Press with left mouse button on the "Conditional Formatting" button on the ribbon.
A popup menu appears.
Press with left mouse button on the "New Rule..." button.
A dialog box appears.
Select "Use a formula to determine which cells to format".
Copy/Paste the following formula in "Format values where this is true:":
=SUMPRODUCT((C6>=INDIRECT("Table1[Start]"))*(C6<INDIRECT("Table1[End]")))
Press with left mouse button on the "Format..." button. A new dialog box shows up.
Press with left mouse button on tab "Fill" on the dialog box.
Pick a color.
Press with left mouse button on OK button.
Press with left mouse button on OK button to return to the worksheet.

3. Identify gaps in a weekly schedule

Schedule category

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

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

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

See all articles in 'schedule' category

Functions in this article

SMALL IF FREQUENCY ROWS ROW LET TOCOL SUMPRODUCT INDIRECT

More than 1300 Excel formulas

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

2 Responses to “Find empty hours in a weekly schedule”

john dalton says:

April 2, 2014 at 11:32 am

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

Reply
Yannie says:

January 5, 2023 at 8:19 pm

Hi Oscar,

Thank you for your in depth description to tackle this problem.

I am not understanding step1 where you put ROW($1:$168)/24-1/(24*60)

If I had a schedule with 5 minute increments, how will that change the formula?

Thank you for your help,
Y

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