Create Monthly Calendars in Google Sheets (Single & Multi-Cell Formulas)

You can create monthly calendars in Google Sheets using either a single formula for the entire month or multiple formulas for each week.

The former is best if you want to print or refer to calendars within the Sheet, and the latter is useful when you want blank rows between each week for entering notes.

When creating a monthly calendar using a single formula, one of the hurdles you may face is skipping or offsetting the first date in a month to the correct day of the week column.

I offer a unique solution for creating a dynamic monthly calendar in Google Sheets using single and multi-cell formulas.

Dynamic Monthly Calendar Formula in Google Sheets

How to Create a Monthly Calendar Using a Single Formula in Google Sheets

Enter the month number in cell A2 and the year in cell B2.

Then, input the days of the week from Sunday to Saturday in cells C2:I2.

To create the monthly calendar, enter the following array formula in cell C3:

=LET(
   start, DATE(B2, A2, 1), 
   seq, SEQUENCE(DAY(EOMONTH(start, 0))), 
   test, 
      IF(WEEKDAY(start)=1, seq, 
      VSTACK(WRAPCOLS(,WEEKDAY(start)-1), seq)
   ), IFNA(WRAPROWS(test, 7))
)

This formula will generate the entire month’s calendar at once!

It’s a magical formula; you don’t need to format the result, apply conditional formatting to mask dates, or anything else. The formula will take care of everything.

When you enter a new month number in cell A2, the calendar will refresh. The same is true for the year in cell B2. You can also use data validation drop-downs in cells A2 and B2 to quickly select the month and year.

Monthly Calendar Formula Breakdown (Unleash the Power of Dynamic Calendars)

Some of you may want to learn the formula to improve your Google Sheets skills. Here is how the single formula dynamically populates a full month’s calendar in one go with the proper weekday offset in the first row.

There are four major parts in the formula that create a dynamic monthly calendar in Google Sheets: Sequence Part, Offset Part, Combining Offset and Sequence, and Wrap to Rows Part.

Sequence Part: Creating a Sequence of Days in the Month

First of all, we need to find the month start date from the month and year in cells A2 and B2. The following formula does that:

=DATE(B2, A2, 1)

Now, we need to determine the total days in that month. For that, we find the month-end date using the EOMONTH function:

=EOMONTH(DATE(B2, A2, 1), 0)

If we wrap the above formula with the DAY function, we get the last day in the selected month and year:

=DAY(EOMONTH(DATE(B2, A2, 1), 0))

Wrap it with SEQUENCE to get the days in the whole month, i.e., sequence numbers from 1 to the total days in the month:

=SEQUENCE(DAY(EOMONTH(DATE(B2, A2, 1), 0)))

Offset Part: Adjusting Days Based on the Days of the Week

This is a key part of creating a dynamic monthly calendar using a single formula in Google Sheets.

If the month start date begins on Sunday, we need to offset the above sequence by 0 cells. If it starts on Monday, offset by 1 cell, and so forth. How do we do that?

=WEEKDAY(DATE(B2, A2, 1))-1

The above WEEKDAY formula will return the number to offset. It returns the weekday number – 1, resulting in 0 for Sunday, 1 for Monday, and so on.

Wrapping it with the WRAPCOLS function, as shown below, will return n error cells, and that is the Offset Part:

=WRAPCOLS(,WEEKDAY(DATE(B2, A2, 1))-1)

Combining Offset (N Error Cells) and Sequence (Days in a Month)

Next, we want to stack it (Offset Part result) with the sequence numbers (Sequence Part result) only if the weekday number is not equal to 1 (not equal to Sunday).

Adjusting Dates Based on the Day of the Week in Google Sheets

Here is the generic formula that uses an IF logical test:

=IF(WEEKDAY(DATE(B2, A2, 1))=1, sequence_part, VSTACK(offset_part, sequence_part))

So the corresponding formula will be:

=IF(WEEKDAY(DATE(B2, A2, 1))=1, SEQUENCE(DAY(EOMONTH(DATE(B2, A2, 1), 0))), VSTACK(WRAPCOLS(,WEEKDAY(DATE(B2, A2, 1))-1), SEQUENCE(DAY(EOMONTH(DATE(B2, A2, 1), 0)))))

Wrap to Rows Part: The Final Step in Creating a Dynamic Monthly Calendar in Google Sheets

To create the dynamic monthly calendar, you just need to use the WRAPROWS function to wrap the above formula to wrap the output by 7 cells and use IFNA to replace error cells with blank.

=IFNA(WRAPROWS(IF(WEEKDAY(DATE(B2, A2, 1))=1, SEQUENCE(DAY(EOMONTH(DATE(B2, A2, 1), 0))), VSTACK(WRAPCOLS(,WEEKDAY(DATE(B2, A2, 1))-1), SEQUENCE(DAY(EOMONTH(DATE(B2, A2, 1), 0))))), 7))

That is our single-cell formula for creating a dynamic monthly calendar in Google Sheets.

But you will notice a different formula before the sub-title ‘Monthly Calendar Formula Breakdown’ above. That’s because we have used the LET function there, so that we can avoid using the same expressions more than once in the formula, for example, the SEQUENCE and DATE.

Creating a Monthly Calendar Using Separate Formulas for Each Week

If you want blank rows between each week in the calendar, you need to resort to separate formulas for each week.

Don’t worry; we have a single-cell magical formula that creates a dynamic monthly calendar in Google Sheets. You need to use the CHOOSEROWS function with it to choose the row you want.

In our one-piece code for the whole month, we need to replace the IFNA(WRAPROWS(test, 7)) part with IFERROR(CHOOSEROWS(WRAPROWS(test, 7), n)).

The n will be 1 in the first row, 2 in the second row, and so forth. You need n up to 6.

=LET(
   start, DATE(B2, A2, 1), 
   seq, SEQUENCE(DAY(EOMONTH(start, 0))), 
   test, 
      IF(WEEKDAY(start)=1, seq, 
      VSTACK(WRAPCOLS(,WEEKDAY(start)-1), seq)
   ), IFERROR(CHOOSEROWS(WRAPROWS(test, 7), n))
)

Conclusion

You might have come across various formulas in Excel as well as Google Sheets for creating a monthly calendar or template. Our formula stands out from the rest due to its simplicity and flexibility.

Many of the formulas offered use multiple formulas, which may require additional steps like highlighting or formatting.

In our case, you can use the formula straight out of the box. Simply insert the month and year into designated cells (A2 and B2), type the days of the week from Sunday to Saturday in C2:I2, and copy-paste our formula. The formula will take care of the rest.

You can also split the same formula into multiple rows using the CHOOSEROWS function. If you are interested in customized templates, check out these tutorials:

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Filter the Last 7 Days in Excel Using the FILTER Function

You may have records spanning several days and want to filter the last 7...

Find Missing Dates in Excel

You can find missing dates in a list in Excel using either a VLOOKUP...

Generate Unique Random Numbers in Excel with Dynamic Arrays

Unique random numbers refer to random numbers without duplicates. Generating such numbers is straightforward...

Lookup Previous Values Dynamically in Excel and Google Sheets

Sometimes, you may want to look up the previous values relative to the current...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

2 COMMENTS

  1. Thanks so much for this! Exactly what I needed and worked like a charm. I ended up hardcoding the date format within the formula with LAMBDA instead of relying on the sheet formatting options:

    =LAMBDA(X, ARRAYFORMULA(IF(ISBLANK(X), X, TEXT(X, "D"))))([your formula])

    Just putting it here in case anyone else can find it useful. Again, thank you so much for your work and explanation. Have a great day!

    • Hi Josh,

      Thank you for sharing the information. Please check back in 1-2 days. We can now employ some newer functions to streamline the formulas. I’ll update the tutorial as soon as possible.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.