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

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

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.

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

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

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

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

## 6. Conditional formatting

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

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

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?

### Get the Excel file

Calendar-monthly-view3.xlsm

## 8. Big version

This bigger version has 10 rows per day.