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

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

Back to top

How to copy the array formula

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

Back to top

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.

Back to top

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

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

and returns

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

Back to top

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]")))

Back to top

3.2 How to create Conditional Formatting

Highlight nonempty hours in a schedule

  1. Select cell range C6:I29.
  2. Go to tab "Home" on the ribbon.
  3. Press with left mouse button on the "Conditional Formatting" button on the ribbon.
    CF button
  4. A popup menu appears.
    CF button new rule
  5. Press with left mouse button on the "New Rule..." button.
  6. A dialog box appears.
    CF button new rule use a formula to determine which cells to format
  7. Select "Use a formula to determine which cells to format".
  8. Copy/Paste the following formula in "Format values where this is true:":
    =SUMPRODUCT((C6>=INDIRECT("Table1[Start]"))*(C6<INDIRECT("Table1[End]")))

    CF Format values where this is true

  9. Press with left mouse button on the "Format..." button. A new dialog box shows up.
  10. Press with left mouse button on tab "Fill" on the dialog box.
    CF Format values where this is true fill color
  11. Pick a color.
  12. Press with left mouse button on OK button.
    CF Format values where this is true
  13. Press with left mouse button on OK button to return to the worksheet.

Back to top

3. Identify gaps in a weekly schedule

Back to top