Populate cells dynamically in a weekly schedule

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)

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

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

Populate multiple cell values in a single cell in a weekly schedule (vba)

What should I change if I have two different which start and end the same date time ? As it is now, only the first one is displayed. How I can divide them in order to see both of them?

VBA code

Function Lookup_concat(SearchDate As String, _
StartDate As Range, EndDate As Range, Return_val_col As Range)
Dim i As Long
Dim result As String
For i = 1 To StartDate.Count
If (StartDate(i, 1) * 1) <= SearchDate Then
If (EndDate(i, 1) * 1) >= SearchDate Then
result = result & Return_val_col.Cells(i, 1).Value & " | "
End If
End If
Next i
result = Left(result, Len(result) - 3)
Lookup_concat = Trim(result)
End Function

Where do I copy/paste vba code?

Press Alt-F11 to open visual basic editor
Select your workbook in project explorer
Click Module on the Insert menu
Copy and paste the above user defined function
Exit visual basic editor