Weekly Calendar

In this tutorial, we’re going to learn how to create a dynamic weekly calendar in Excel that shows which programs or events are running on any given day. This technique is perfect for managing schedules where events have different start and end dates and may only occur on certain weekdays.

Objective

We are trying to get Excel to dynamically create a weekly calendar like this:

From an events table like this:

Let’s jump right into it!

Video

https://youtu.be/xqr99ygjRDc

Step-by-step Guide

Exercise 1: Getting Familiar with Key Excel Functions

Before building our calendar, let’s warm up with some Excel functions that we’ll be using. These include SEQUENCE, WEEKDAY, ISNUMBER, FIND, FILTER, ARRAYTOTEXT, TEXTJOIN, and CHAR. Don’t worry if you’re not familiar with these yet – I’ll break them down for you.

1. The SEQUENCE Function

The SEQUENCE function generates a series of numbers in a specific order. Here’s how it works:

• Syntax: =SEQUENCE(rows, columns, start, step)
• Example:

  =SEQUENCE(1, 7, 1, 1)

This creates a sequence from 1 to 7 in one row.

Try changing the starting number to 10 or 50 to see how the sequence adapts!

2. The WEEKDAY Function

The WEEKDAY function helps us identify the day of the week for any given date.

• Syntax: =WEEKDAY(date, [return_type])
• Example:

  =WEEKDAY(A2)

This function will return a number from 1 to 7, where 1 usually represents Sunday. However, you can customize the start of the week using the return_type argument.

3. The ISNUMBER Function

The ISNUMBER function checks if a value is a number and returns TRUE or FALSE.

• Syntax: =ISNUMBER(value)
• Example:

  =ISNUMBER(A2)

This will return TRUE if cell A2 contains a number and FALSE otherwise.

4. The FIND Function

The FIND function locates a substring within another string.

• Syntax: =FIND(find_text, within_text)
• Example:

  =FIND("a", "apple")

This returns 1 since ‘a’ is the first character.

5. The FILTER Function

The FILTER function helps extract data based on certain criteria.

• Syntax: =FILTER(array, include)
• Example:

  =FILTER(A2:A10, B2:B10 = 1)

This returns all values in column A where the corresponding row in column B equals 1.

6. The ARRAYTOTEXT Function

The ARRAYTOTEXT function converts an array into a text string, using commas by default.

• Syntax: =ARRAYTOTEXT(array, [format])
• Example:

  =ARRAYTOTEXT(FILTER(A2:A10, B2:B10=1))

7. The TEXTJOIN Function

TEXTJOIN is a more customizable way to combine text with a specific delimiter.

• Syntax: =TEXTJOIN(delimiter, ignore_empty, text1, [text2],...)
• Example:

  =TEXTJOIN(", ", TRUE, A2:A10)

8. The CHAR Function

The CHAR function returns the character for a given number. CHAR(10) inserts a line break.

• Example:

  =CHAR(10)

With a basic overview of the functions we’ll use complete, let’s see how we combine them to create our calendar.

Exercise 2: Building Our Weekly Calendar

Now that we’re familiar with these functions, it’s time to create our weekly calendar.

Step 1: Setting Up the Dates

We enable the user to enter the desired start date for the calendar in a cell, such as B6:

Then we can reference that start date in our SEQUENCE function to create a row that contains the dates for our calendar. For example, we can write the following formula:

=SEQUENCE(1, 7, B6, 1)

When we write the formula into B9, it will generate a series of seven dates starting from the date entered in cell B6 like this:

And, when you change the start date in B6, the weekly calendar dates update accordingly. Now that we have the dates for the week stored in cells, we can retrieve the events from our events table.

Step 2: Using FILTER to Retrieve Events

We store the events in our events table, named Table1, which looks like this:

To pull events from the table into our calendar based on their start and end dates, we’ll write the following formula into B10:

=FILTER(Table1[Title],((B9>=Table1[Start])*(B9<=Table1[End])))

This formula written into B10 and filled right through H10, returns events occurring on the calendar dates, like this:

When a day has multiple events, they are returned to separate cells, as seen in E10 and E11. If we want all events for the day to be returned into a single cell, we can use an additional function to help. Let’s examine that next.

Step 3: Combining Events with TEXTJOIN or ARRAYTOTEXT

If multiple events occur on the same day, we can combine them as a comma-separated list by wrapping the ARRAYTOTEXT function around the FILTER function like this:

=ARRAYTOTEXT(FILTER(Table1[Title], (Table1[Start]<=B9) * (Table1[End]>=B9)))

This formula lists multiple events on separate lines within the same cell:

If we want to further customize the results, for example by using a different delimiter, we can use the TEXTJOIN function instead of ARRAYTOTEXT. For example, if we want to have each event on its own line within the cell, we can use CHAR(10) as the first argument to use a line break delimiter, and write the following formula instead:

=TEXTJOIN(CHAR(10),TRUE,FILTER(Table1[Title],((B9>=Table1[Start])*(B9<=Table1[End]))))

And, provided the cell formatting is set to Wrap Text and the row height is tall enough to display the lines, the updated calendar looks like this:

But, what if we want to specify the days of the week for each event? We tackle that in the next exercise.

Exercise 3: Adding Weekday-Specific Events

To enable our calendar to display events for specific weekdays, we’ll first need to update our events table to include a column for weekdays. Rather than use alpha codes like M, T, W … we’ll use numeric codes like 1, 2, 3 just to make our formula a bit shorter:

In the table above, 1 represents Sunday, 2 Monday, 3 Tuesday, and so on. So 234 means this event is scheduled for every Mon, Tue, and Wed between the start and end dates. We incorporate the WEEKDAY function into our formula to get the week day of the date.

=FILTER(Table2[Title], ((B9>=Table2[Start]) * (B9<=Table2[End]) * (ISNUMBER(FIND(WEEKDAY(B9),Table2[Weekday])))),"")

Here is a quick explanation of how the second argument of the FILTER function in the formula above works:

a. (B9 >= Table2[Start])

• Checks if the value in cell B9 is greater than or equal to the corresponding “Start” date in Table2.

b. (B9 <= Table2[End])

• Checks if the value in cell B9 is less than or equal to the corresponding “End” date in Table2.

c. ISNUMBER(FIND(WEEKDAY(B9), Table2[Weekday]))

• WEEKDAY(B9): Returns the weekday number (1 for Sunday, 2 for Monday, etc.) for the date in B9.
• FIND(WEEKDAY(B9), Table2[Weekday]): Tries to find this weekday number within the “Weekday” column in Table2. If found, it returns a number; otherwise, it returns an error.
• ISNUMBER(...): Converts this into TRUE if the weekday is found and FALSE otherwise.

Note: The * acts as an “AND” operator in Excel, meaning all conditions must be TRUE for a row to be included. Therefore, the filter will include rows where:

• The date in B9 is between the “Start” and “End” dates.
• The weekday of B9 matches one of the weekday numbers in Table2[Weekday].

When there are multiple events per day, we can return the events into a single cell by wrapping the ARRAYTOTEXT or TEXTJOIN functions around the filter function, like this:

=ARRAYTOTEXT(FILTER(Table2[Title], ((B9>=Table2[Start]) * (B9<=Table2[End]) * (ISNUMBER(FIND(WEEKDAY(B9),Table2[Weekday])))),""))

Now, your weekly calendar is set up to handle start/end dates and specific weekdays, and with a bit of cell formatting looks like this:

The best part is that this is fully dynamic. So, anytime the values in the events table change, or new events are added, they automatically flow into the calendar. Plus, you can change the Start date at will and the calendar updates accordingly. Nice!

Conclusion

That’s it! You now have a fully functional weekly calendar in Excel that dynamically updates based on your data. This technique is a powerful way to visualize your schedule and ensure you’re always on top of your events.

Feel free to try it out, and let me know if you have any questions!

Sample File

FAQ

Q1: What if I want to start the week on Monday instead of Sunday?

Use the WEEKDAY function’s second argument. For example, =WEEKDAY(date, 2) makes Monday = 1.

Q2: Can I use this method for a monthly calendar?

Yes! Just create a pair of formulas for each week. The first formula uses the SEQUENCE function to generate the days. The second formula retrieves the events. And you create pairs of formulas for each week you’d like to display.

Q3: How do I add more events to my calendar?

Simply add rows to your data table with the new event details.

Q4: Why does my FILTER function return an error?

Check that your criteria match the data type (e.g., text vs. numbers) and make sure there are no mismatched ranges.

Q5: Can I customize the delimiter used in TEXTJOIN?

Absolutely! Replace the first argument with any delimiter you prefer, such as ":" for colons.

Q6: Why is the ISNUMBER function used with FIND?

ISNUMBER(FIND(...)) helps verify if a value exists. It handles errors gracefully when a match isn’t found.

Q7: How do I print this calendar?

Set the print area in Excel and adjust page settings for a clean, printable view.