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 containsinformation about day intervals.

Recommended article:

Quickly create new sheets [VBA]

The following macro let´s you select a cell range and then the macro creates sheets with the same names as […]

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 a dynamic named range

In this post I am going to explain the dynamic named range formula in Sam's comment. The formula adds new rows and columns […]

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!

Recommended post:

Learn this genius trick on how to use a Table name in Data Validation Lists and Conditional Formatting formulas

David Hager gave this valuable comment about how to reference a table name in conditional formatting formulas: =INDIRECT("Table1[Start]") Watch this video to […]

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.

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: […]

Named ranges

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

I recommend using an excel defined table, easier to setup and work with:

Become more productive – Learn Excel Defined Tables

An Excel table allows you to easily sort, filter and sum values in a data set where values are related.

Download excel template

Schedule recurring events-in-a-weekly-schedule.xlsx
(Excel 2007 Workbook *.xlsx)

Functions in this article

IF function explained

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

SMALL function and LARGE function

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

INDEX function explained

Fetch a value in a data set based on coordinates.

How to use Excel’s MATCH function

Identify the position of a value in an array.

IFERROR function

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

 

ROW(reference)
Returns the rown umber of a reference

 

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