In this post I am going to add one more function to a weekly schedule, an array formula allows you to populate cells with information from a schedule sheet.

The first part in this article demonstrates this array formula, the second part in this post shows you how to show multiple events in a cell with the help of some vba code.

Table of Contents

  1. Populate cells dynamically in a weekly schedule
  2. Populate multiple cell values in a single cell in a weekly schedule (vba)

Populate cells dynamically in a weekly schedule

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.

Go to these articles if you want to know more about the functions in the array formula above:

IFERROR function

The IFERROR function was introduced in excel 2007. In previous excel versions you could check for errors with the ISERROR […]

Comments(0) Filed in category: Excel

INDEX function explained

Fetch a value in a data set based on coordinates.

Comments(14) Filed in category: Cross reference table, Excel, INDEX function

SMALL function and LARGE function

This function lets you extract any number in a cell range based on sort rank.

Comments(12) Filed in category: Excel

IF function explained

Check if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

Comments(9) Filed in category: Excel

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)

Recommended reading

Setting up your work hours in a weekly schedule in excel

Hours outside workhours are filled with grey using conditional formattting, except weekends. Conditional formatting formula applied on cell range C6:I29: […]

Comments(0) Filed in category: Excel, Schedule

Find empty hours in a weekly schedule in excel

In this post, I have created som random time ranges. We are going to use these time ranges to extract […]

Comments(1) Filed in category: Excel, Schedule

Schedule recurring events in a weekly schedule in excel

In our sequel about weekly schedule it is now time to create a recurring events. This is what we have […]

Comments(12) Filed in category: Excel, Schedule

I recommend reading this article if you are looking for a calendar with a monthly view:

Calendar – monthly view

Tesh asks: How easy is it to modify this for recurring tasks (weekdays, weekly, monthly, quarterly and yearly) and maybe […]

Comments(22) Filed in category: Calendar, Excel, Templates


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

Katerina Georgiadou asks:

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?

Answer:

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) &lt;= SearchDate Then If (EndDate(i, 1) * 1) &gt;= SearchDate Then
            result = result &amp; Return_val_col.Cells(i, 1).Value &amp; " | "
        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

Download excel *.xlsm file

Populate-time-ranges-in-a-weekly-schedule-version2.xlsm