Crafting a Dynamic Monthly Calendar with a single formula

This article walks through step-by-step how to create a dynamic monthly calendar display using a single formula.

Our final output will look like this.

Understanding the Basics

Consider, in our example, that the year and month inputs are in cells C2 and C3 respectively.

To generate a dynamic calendar with these inputs, we follow a 3-step approach:

1. Find the Sunday of the starting week.
2. Generate the sequence of dates.
3. Highlight only the current month's dates using Conditional Formatting

?

Step-by-Step Guide

1. Find the Sunday of the starting week.

We will get the first Sunday of the starting week of month by using the DATE and WEEKDAY functions.

The DATE function takes as arguments a year, month, and date and returns the corresponding date.

=DATE (YEAR, MONTH, DAY)

The WEEKDAY function takes as input a date and returns its corresponding number.

=WEEKDAY (date, optional_return_type)

In our example, year and month inputs are in cells C2 and C3, our formula would be:

If you would like to further understand this formula, I have a separate video explaining that.

this

Quick tip: For absolute cell references, lock the cells with the $ symbol as in the above example. This can be achieved by pressing F4 while writing the formula.

?

This gives us the Sunday of the first week of Jan 2024 in cell D6:

?

2. Generate the sequence of dates.

With the first Sunday in hand, all we need to do is use the SEQUENCE function to generate a sequence of calendar dates from the first Sunday.

=SEQUENCE (no_of_rows, [no_of_cols], [starting_point], [increments])?

In our example, we need a matrix of dates that resembles a calendar. For every month, there can be a maximum of 6 weeks (rows) and a week has 7 days (columns).

The starting point will be the first Sunday from step 1 and increment 1 day as we need all the days.

With this, our formula will be:

This generates the entire 6 weeks of the calendar as shown:

3. Highlight only the current month's dates using Conditional Formatting

For our final step, let us make our calendar look aesthetically nice. That is, we will highlight only the dates in Jan 2024.

This is done by using conditional formatting:

a.????? Select all the cells in our calendar, go to Conditional Formatting, and select a new rule for these cells.

b.????? Use a Formula as a rule, where we use the MONTH function where the month of the cell (from D6 onwards) is not equal to the input month in cell C3.

This formula applies conditional formatting only in the cells where the dates do not belong to the current month.

Note that while referring to cells in the formula, Excel automatically locks the reference with a $ symbol, edit the same wherever applicable.

?

c.?????? Now, we apply any format of choice to these cells and adjust the appearance according to our preference. Here, we’ve chosen the other month's dates to appear less prominent than the dates of the current month by adjusting the fonts:

Now, our final, dynamic calendar will look like this:

The Single formula

The single formula we used to generate the calendar.

Functions Used in this article: SEQUENCE, DATE, WEEKDAY, MONTH

Engage with Us

With your dynamic monthly calendar ready, how do you plan to use it? Do you have suggestions for enhancing the calendar or any queries on how to implement additional features? Share your thoughts and questions in the comments section.

Subscribe to our Weekly Newsletter

?