In this post we are going add one more function to a weekly schedule. We are going to create an array formula to populate cells with information from a schedule sheet.

Here is a picture of the schedule sheet:

From the above picture we understand:

On the 1 of August from 8:00 AM to 10:00 AM the word "Meeting" will populate two cells on weekly schedule.

On the 1 of August from 1:00 PM to 3:00 PM the word "Design" will populate two cells on weekly schedule.

On the 3 of August from 12:00 PM to 4:00 PM the word "Strategies" will populate four cells on weekly schedule.

Here is a picture of the weekly schedule and the populated cells:

Now let us see what happens if we change the date in cell F2 to 9-Aug-2010.

Cell range C4:I4 have new dates. They are dynamic and change depending on the value in cell F2.

The cells in C6:I29 are all empty now. They are also dynamic and change depending on the dates  on cell range C4:I4. The cells are empty because nothing is scheduled on these dates. See picure of schedule sheet at the top.

Array formula in C4:

=$F$2-WEEKDAY($F$2;1)+1 + Enter

Formula in D4:

=C4+1 + Enter.

Copy cell C5 and paste it into cells E4:I4

Array formula in C6:

=IFERROR(INDEX(Title, SMALL(IF(((C$4+$B6)>=Start)*((C$4+$B6)<End), ROW(Start)-MIN(ROW(Start))+1, ""), 1)), "") + CTRL + SHIFT + ENTER.

Copy cell C6 and paste it into cell range C6:I29.

Conditional formatting

I filled populated cells using conditional formatting.

Named ranges

Start (E2:E5)
End (F2:F5)
Title (B3:B5)

Download excel template

Populate-time-ranges-in-a-weekly-schedule.xlsx
(Excel 2007 Workbook *.xlsx)

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

SMALL(array,k) returns the k-th smallest row number in this data set.

ROW(reference) returns the rownumber of a reference

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value

IFERROR(value;value_if_error) Returns value_if_error if expression is an error and the value of the expression itself otherwise

WEEKDAY(serialnumber;[return_type])
Returns a number from 1 to 7 identifing the day of the week of a date

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text