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:
Excel 365 dynamic array formula in cell B34:
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
Deprecated: Function get_page_by_title is deprecated since version 6.2.0! Use WP_Query instead. in /var/www/html/get-digital-help.com/public_html/wp-includes/functions.php on line 6031
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)
2. Highlight non-empty hours in a weekly 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:
3.1 Explaining CF formula
Step 1 - Create a structured reference to an Excel Table
Table1[Start]
Step 2 - Workaround
Deprecated: Function get_page_by_title is deprecated since version 6.2.0! Use WP_Query instead. in /var/www/html/get-digital-help.com/public_html/wp-includes/functions.php on line 6031
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
Deprecated: Function get_page_by_title is deprecated since version 6.2.0! Use WP_Query instead. in /var/www/html/get-digital-help.com/public_html/wp-includes/functions.php on line 6031
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
- 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
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 schedule uses the year and month in cell D1 and K1 to highlight activities like vacation specified in the […]
Excel categories
2 Responses 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.
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
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