Author: Oscar Cronquist Article last updated on June 11, 2021

calendar monthly view

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

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.
calendar-monthly-view

Back to top

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".

calendar-add-event

Back to top

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.

calendar-many-events

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


calendar-many-events1

Back to top

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.

calendar-edit-event

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

Back to top

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"

calendar-change-month

Back to top

6. Conditional formatting

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

calendar-conditional-formatting

Back to top

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.

calendar-recurring-events

Monthly

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.

YEAR(TODAY())

becomes

YEAR(44357)

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.

MONTH(TODAY())

becomes

MONTH(44357)

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(YEAR(TODAY()), MONTH(TODAY())+1, 3)

becomes

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

becomes

DATE(2021, 6+1, 3)

becomes

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

becomes

44380 +11/24

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

Step 6 - Calculate date this month and time

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

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)

Weekly

Array formula in cell H5:

=TODAY()+IF(WEEKDAY(TODAY())<=3, 3-WEEKDAY(TODAY()), (10-WEEKDAY(TODAY())))+15/24

Daily

Array formula:

=TODAY()+17/24

Anyone got a better idea?

Back to top

Get the Excel file


Calendar-monthly-view3.xlsm

8. Big version

This bigger version has 10 rows per day.

Back to top

Back to top