Author: Oscar Cronquist Article last updated on February 08, 2023

calendar monthly view

This article describes how to build a calendar showing all days in a chosen month with corresponding scheduled events.

1. Calendar, monthly view - Excel 365

Calendar monthly view 1

The image above demonstrates a calendar built for Excel 365, it doesn't contain any VBA macros. Everything is built on new Excel 365 functions and some Form Controls.

The calendar gets the information from an Excel Table located next to the calendar based on the displayed date in cell C2. You can change the date shown in cell C2 by pressing the left mouse button on the spin buttons next to year and month.

Calendar monthly view Excel Table


The first cell in each date box contains a formula, it spills data to the cells below automatically if needed. The formula returns a #SPILL! error if there are more data to show than rows.

Formula in cell B7:

=FILTER(Table1[[Time and Title]:[Time and Title]],INT(Table1[[Date and time]:[Date and time]])=B6,"")

Explaining formula in cell B7

Step 1 - Specify an absolute structured reference to an Excel Table named Table1

A cell reference to a column in an Excel Table is called a structured reference. When you copy a cell and paste to another cell the structured reference changes, in order to lock or create an absolute structured reference you need to use a colon and references before and after the colon.

A regular structured reference: Table1[Date and time]

An absolute structured reference: Table1[[Date and time]:[Date and time]]

Step 2 - Remove decimals or time from an Excel date and time value

The INT function removes the decimal part from positive numbers and returns the whole number (integer) except negative values are rounded down to the nearest integer.

Function syntax: INT(number)

INT(Table1[[Date and time]:[Date and time]])


INT({45231.5416666667; 45231.7083333333; 45232.3333333333;  ... ; 2555})

and returns

{45231; 45231; 45232;  ... ; 2555}

Step 3 - Remove decimals or time from an Excel date and time value

The equal sign lets you compare value to value in an Excel formula. You can also use it to compare values in an array, the result in both cases are boolean values TRUE or FALSE.

INT(Table1[[Date and time]:[Date and time]])=B6


{45231; 45231; 45232;  ... ; 2555}=45228

and returns


Step 4 - Filter values based on boolean values

The FILTER function extracts values/rows based on a condition or criteria.

Function syntax: FILTER(array, include, [if_empty])

FILTER(Table1[[Time and Title]:[Time and Title]],INT(Table1[[Date and time]:[Date and time]])=B6,"")


FILTER(Table1[[Time and Title]:[Time and Title]],{FALSE; FALSE; FALSE;  ... ; FALSE},"")

and returns nothing "". All values are filtered out and the FILTER function returns #CALC! error, however, the third argument in the FILTER function lets you specify what to return if that happens.

In this case, "" nothing is returned which displays a blank cell.

Get the Excel file


2. Calendar, monthly view - VBA

Tesh asks:
How easy is it to modify this for recurring tasks (weekdays, weekly, monthly, quarterly and yearly) and maybe show a monthly view? Times are less important than just showing what is due on what day.

I made a calendar shown below, monthly view. The picture is resized to fit this blog, press with left mouse button on to see the original size. This calendar is more advanced than the template I made year 2011.

Back to top

2.2. Add event

The form next to the calendar allows you to add events. Enter time and event name and then press with left mouse button on button "Add".


Back to top

2.3. See all events on a specific date

If there are more events on a single day than can be displayed, the last line tells you ...more.... See picture below for an example.


Select that cell and all events are shown in a table next to the calendar.


Back to top

2.4. Edit event

You can easily edit or delete an event by press with left mouse button oning a link in column Time, see picture above. The link takes you to the record on sheet "Schedule", see picture below.


Here you can edit or delete the record as you please.

Back to top

2.5. Change month - worksheets buttons

The buttons above the calendar lets you go to next or previous month, there is also a button that takes you to the current month, button "Today"


Back to top

2.6. Conditional formatting

Days before and after selected month are grayed out. Current day is highlighted orange. The following picture shows you this.


Back to top

2.7. Recurring events

The best I could do is creating a formula that calculates the upcoming recurring event. Events after that are not shown until the actual date has passed.



Array formula in cell H4:

=IF(DAY(TODAY())>3, DATE(YEAR(TODAY()), MONTH(TODAY())+1, 3)+11/24, DATE(YEAR(TODAY()), MONTH(TODAY()), 3)+11/24)

Explaining formula in cell H4

Step 1 - Calculate current date

The TODAY function returns today's date, it is a volatile function meaning it recalculates each time the worksheet is recalculated.

TODAY() returns 44357 formatted as 6/10/2021.

Step 2 - Calculate year

The YEAR function returns a number representing the year based on an Excel date.




and returns 2021

Step 3 - Calculate month

The MONTH function returns a number representing the relative position. For example, 1 is January, 2 is February, ..., 12 is December.




returns 6. June is the sixth month.

Step 4 - Calculate date next month

The DATE function returns a date based on a year, month, and day number.



DATE(2021, MONTH(TODAY())+1, 3)


DATE(2021, 6+1, 3)


DATE(2021, 7, 3)

and returns 44380 (7/3/2021).

Step 5 - Calculate date next month and time

DATE(YEAR(TODAY()), MONTH(TODAY())+1, 3)+11/24


44380 +11/24

and returns 44380.4583333333 (7/3/2021 11:00 AM).

Step 6 - Calculate date this month and time


Step 7 - Check if today is larger than 3

IF(DAY(TODAY())>3, DATE(YEAR(TODAY()), MONTH(TODAY())+1, 3)+11/24, DATE(YEAR(TODAY()), MONTH(TODAY()), 3)+11/24)


Array formula in cell H5:



Array formula:


Anyone got a better idea?

Back to top

Get the Excel file


2.8. Big version

This bigger version has 10 rows per day.

Back to top

Back to top