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.
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
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!
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.
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
Returns the k-th smallest number in this data set.
Returns the rown umber of a reference
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
Returns value_if_error if expression is an error and the value of the expression itself otherwise
Returns a number from 1 to 7 identifing the day of the week of a date
Returns the smallest number in a set of values. Ignores logical values and text