In our sequel about weekly schedule it is now time to create a recurring events.

This is what we have created in earlier posts:

Here is a picture of some example events on sheet "Schedule". The first event is recurring daily and the second event is recurring weekly.

How to setup data validation list in cell E3:E4 on sheet "Schedule"

Create a new sheet

  1. Create new sheet named "Data"
  2. There are threee options, all in each cell A1:A3. See picture below.
  3. Cell range B1:B3 contains information about day intervals.

Create a named range

  1. Select sheet "Data"
  2. Select cell range A1:A3
  3. Select tab "Formulas" on the ribbon
  4. Click "Named range" button
  5. Create a new named range A1:A3

Create Data validation list

  1. Select sheet "Schedule"
  2. Select cell E3
  3. Select tab "Data" on the ribbon.
  4. Click "Data validation"
  5. Select "List"
  6. Type your named range in "Source:" field.
  7. Click OK!

Array formula

Array formula in C6:

=IFERROR(INDEX(Title, SMALL(IF(((C$4+$B6)>=Start)*((C$4+$B6)<End)+(Rec="Daily")*(C$4>=Start)*($B6>=TIMEVALUE(TEXT(Start, "tt:mm")))*($B6<TIMEVALUE(TEXT(End-(1/86400), "tt:mm")))+(Rec="Weekly")*(C$4>=Start)*(WEEKDAY(C$4)=WEEKDAY(Start))*(WEEKDAY(C$4)=WEEKDAY(End))*((C$4+$B6)>=Start)*($B6>=TIMEVALUE(TEXT(Start, "tt:mm")))*($B6<TIMEVALUE(TEXT(End-(1/86400), "tt:mm"))), ROW(Start)-MIN(ROW(Start))+1, ""), 1)), "") + CTRL + SHIFT + ENTER.

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

Now the first event is recurring every day and the second event is recurring every week.

The grey cells are hours outside workhours.

Named ranges

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

Download excel template

Schedule recurring events-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