## Schedule recurring events in a weekly schedule

*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:

- Weekly schedule template
- How to highlight specific time ranges
- How to find empty hours
- How to populate cells dynamically
- Setting up your work hours

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**

- Create new sheet named "Data"
- There are threee options, all in each cell A1:A3. See picture below.
- 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**

- Select sheet "Data"
- Select cell range A1:A3
- Select tab "Formulas" on the ribbon
- Click "Named range" button
- Create a new named range A1:A3

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

**Create Data validation list**

- Select sheet "Schedule"
- Select cell E3
- Select tab "Data" on the ribbon.
- Click "Data validation"
- Select "List"
- Type your named range in "Source:" field.

- 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:**

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**

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

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.

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

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

Populate cells dynamically in a weekly schedule

In this post I am going to add one more function to a weekly schedule, an array formula allows you […]

Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of […]

Highlight specific time ranges in a weekly schedule

In a previous post I created a simple weekly schedule with dynamic dates. In this post I am going to […]

Weekly schedule template in excel

I would like to share this simple weekly schedule I created. How to use weekly schedule Type any date in cell […]

How to use the TIMEVALUE function

The TIMEVALUE function returns a decimal number based on a text string. Excel uses decimal numbers between 0 and 0.99988426 […]

Select value based on time and date [VBA]

Introduction This blog post describes how to automatically select a value based on time and date using vba in excel. […]

### 15 Responses to “Schedule recurring events in a weekly schedule”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

this array formula stops working when opening in Excel 2010. When I first open the saved file, I can see school and ice hockey, but I'm told editing has been disabled. When I enable editing, the data disappears and I can't figure out why. Sorry.

Brad,

I downloaded the uploaded file and it works here with Excel 2007. There seems to be nothing wrong with the uploaded file. I have no clue why you are told editing has been disabled.

I appreciate the reply. I'm not sure either. Maybe it's a 2010 thing, or maybe my settings.

I'm trying to use your formulas to create my own bill reminder sheet. I envision a workbook where you enter your bills due date and their frequency. However what makes mine different than yours is I'd love for it to auto populate the bills based on my pay periods. I'd want it to list the bills I have to pay with the corresponding check.

In other words I dont care what date my phone bill is due, because I live hand to mouth, so their due date is actually the day I get paid, make sense?

If I get paid on the 5th and then again on the 19th, the sheet should list all the bills I have between the 5th and 19th as bills I need to pay with my check on the 5th.

if I have a bill due on the 18th it should still list it as to be paid from the 5th check because paying with my check on the 19th would be too late. make sense?

just an idea. I've searched for something like this for literally YEARS and finally decided I need to make one myself. Unfortunately, I am horrible with arrays and all around sucky at programming. Which is why I have no idea why your sheet has editing disabled :(

Excel 2010: replace tt:mm with hh:mm in formulas

Fixed.

Brad,

Read this post: Bill reminder in excel

Thanks for commenting!

Hi I am trying to change the recurring to a 4 weekly schedule for rental incone how can I do it?

Hi Oscar

Could you tell me how to incorporate 3 rooms booking for time schedule instead of 1 room booking currently you have in your excel worksheet.

Thanks

Lai

Lai,

Can you provide an example workbook?

Hi,

I can't get the template to work for more than two events/meetings, how can I get more than two meetings to appear in the weekly schedule?

Hi Oscar,

I am having the same issue as Brad (16 feb 2011). Are you able to upload this doc again perhaps in a later format? I am creating a scheduling document which has multiple start date/end dates with recurring schedules that have a visual representation of order of items over a week. I tried implementing your formula above but it doesn't work.

Thanks!

Clem

You have to extend the named ranges if you add values to the table, I know this is not something one wants to do every time you add or delete table data.

So I converted the table to an excel defined table:

https://www.get-digital-help.com/wp-content/uploads/2010/08/Schedule-recurring-events-in-a-weekly-schedulev2.xlsx

Please describe your issue in greater detail if attached file is not working.

Hi Oscar,

Thank you for this. When I make any changes to the 'table1' in your file, the fields disappear. I have uploaded my version, here: https://ufile.io/o1un8 which uses your weekly schedule. If I input the excel formula from the recurring schedule spreadsheet it errors. I was wondering if you might be able to take a look? This is definitely the missing piece of the pie for me! I'd also note that I'd like to retain how my current spreadsheet works (in that it lists multiple items per cell if there are several items at the same time in the schedule) but also add recurring schedules to the mix. I appreciate your help!!

Thanks,

Clem

Excel 2010: replace tt:mm with hh:mm in all formulas - fixed.

Opened in office 365 Version 1706 and if you change any value on the schedule sheet, the sample data disappears on the Weekly Schedule sheet. Just changing a time from 5pm to 6pm makes all the data disappear. Any ideas why this is happening?

FIX FOR EXCEL 2010 AND LATER:

replace tt:mm with hh:mm in all formulas.