Author: Oscar Cronquist Article last updated on February 26, 2018

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

A dynamic named range grows automatically when new values are added and also shrinks if values are deleted. This saves […]

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:

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

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

How to use the IF function

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

How to use the SMALL function

The SMALL function lets you extract a number in a cell range based on how small it is compared to the other numbers in the group.

How to use the INDEX function

Gets a value in a specific cell range based on a row and column number.

How to use the MATCH function

Identify the position of a value in an array.

How to use the IFERROR function

If the value argument returns an error, the value_if_error argument is used. If the value argument does NOT return an error, the IFERROR function […]

 

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