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

Comments(0) Filed in category: Excel

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 excel

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

Comments(12) Filed in category: Excel, Named range

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

Comments(30) Filed in category: Data validation, Drop down lists, Excel, Excel table

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

Comments(0) Filed in category: Excel, Schedule

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.

Comments(0) Filed in category: Excel, Excel table

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.

Comments(9) Filed in category: Excel

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

INDEX function explained

Fetch a value in a data set based on coordinates.

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

MATCH function

Identify the position of a value in an array.

Comments(12) Filed in category: Excel

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

 

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