How To Make A Calendar In Google Sheets

You're about to learn how to make a calendar in Google Sheets using a single formula.
Here's the sheet if you'd like a copy:

Single Formula Calendar
You can input a month and year to see one formula produce a calendar for that month
Get SheetLet's do this.
How To Make A Single Formula Calendar In Google Sheets
Before you start
Although it's exciting to get started creating something, you should always pause and think about what you'd like to make before you dive headfirst into it.
Thinking about the functionality you want at the start can save you time later in development.
Let's make a list:
- User can input month and year
- Calendar is populated using one formula
- Today's date is highlighted
- When viewing the current month, completed dates are
struck through - Button for the user to return to the current month
Now let's add some detail:
- User can input month and year
- Two dropdowns with data validations
- Calendar is populated using one formula
- Formula needs to populate a seven column, six (max) row matrix with dates for the dropdown provided month and year
- Formula must output actual dates (not just day numbers) for conditional formatting below
- Today's date is highlighted
- Conditional formatting
- When viewing the current month, completed dates are
struck through- Conditional formatting
- Button for the user to return to the current month
- Checkbox with onEdit(e) Apps Script function to return to current date
That's everything. Time to start building.
Build The Frame
You know what you want so make it in your blank sheet:
- Insert two dropdowns
- Insert a checkbox labeled 'Today'
- Write out the names of the days across seven columns
- Leave six rows under the days for the formula to create a calendar
You can also make things look better by:
- Changing the font (I'm using Inter)
- Adding some color
- Bolding some text
- Including a border
Here's what yours could look like at this point:

Create The Formula
This formula is complex. Let's go through it step-by-step.
First you need to generate a seven column, six row matrix.
A calendar requires six rows because if the first of the month is in the last column of the first row you will need room for up to 30 more days.
30/7 = 4.28. You need five more rows after the first to fit these 30 days.
The SEQUENCE function is a good place to start:
- rows = number of rows.
- [columns] = number of columns. If omitted, [columns] = 1.
- [start] = first number of the sequence. If omitted, [start] = 1.
- [step] = amount to increase/decrease each number in the sequence. If omitted, [step] = 1.
Only the first of these arguments is required so something as simple as:
Will get you this (with the formula in the selected cell):

That's looking pretty good, but it's not a calendar just yet because the numbers are, well… numbers - not dates.
Let's add a [start] argument to your SEQUENCE function and include the date as chosen by the user in the dropdowns:
You can't just use the text from the cells but instead have to convert it to a date using the DATEVALUE function and including the text "July2021" as an argument.
You don't need to include a 1 in the text argument for the DATEVALUE function (e.g. "1July2021").
By default the function assumes that you want the first day of the month to be returned.
Now you're getting somewhere:

You've got the dates from the chosen month, but they're not quite right.
The first of July 2021 was a Thursday and this calendar says it was a Sunday.
How can we fix this?
You need to start the sequence in the previous month so that the first lines up with the right day.
Google Sheets has your back with the WEEKDAY function:
- date = the date (obviously).
- [type] = a number (1 (the default if omitted), 2, or 3) indicating how to number weekdays where:
- Sunday is 1 and Saturday is 7.
- Monday is 1 and Sunday is 7.
- Monday is 0 and Sunday is 6.
In the example of July 2021, you need the sequence to start four days before the first (so the first is in Thursday's column - the fifth one).
Which of the types returns 4 when the day is Thursday?
Type 2:
- Monday
- Tuesday
- Wednesday
- Thursday
- Friday
- Saturday
- Sunday
Let's add this to your growing formula:
To get a very calendar-looking output of:

The month of July 2021 lines up perfectly with the days of the week.
But we can see dates from June and August.
Let's fix that.
The identifying quality of the dates you want is that they all share the same month.
You could use an IF function:
To say:
Because the IF function will be taking in an array of values (multiple rows and/or columns) you'll need to wrap it in an ArrayFormula function.
Once again Google Sheets has a built in function for this, the MONTH(date) function.
First let's write your logical_expression:
You compare the month of each returned value (including those in June and August) to see if it's not equal to (<>) the current month.
You can use the opposite logic here to see if the returned values are equal (=) to the current month.
I don't do this because you'd need to include an empty string ("") as the value_if_false.
By using not equal to (<>) I take advantage of the fact that if the value_if_true is left blank in an IF function, the empty string is assumed and not required.
If you put it all together you get:
To get:

Now just change the number formatting to show only the day portion of the date by going to
➜ ➜ ➜ and recreating this:
Et voila, a calendar from a single formula:

Add Conditional Formatting
Today Highlight
As a nice extra feature, you'll want to highlight today's date:

To make this happen, select all of the cells that can contain calendar dates (not just the ones that do right now).
This is from the cell under the Sunday label, six rows down and seven rows across.
Then go to Format cells if... choose the option.
➜ and in the sidebar underIn the text box that appears you want to enter a formula that outputs TRUE for cells you want to affect.
When doing this you want to relatively reference (no $) the top-left-most cell in the selection (in this example that's C5).
For highlighting today's date, you just check if the cell is equal to the TODAY() function:
Choose your desired highlight formatting under Formatting style and you're done.
Past Strikethrough

This conditional formatting follows the same pattern as the today highlight just with a different formula.
Here you want a formula that outputs TRUE when the date in a cell has already happened AND is for the current month (striking through all past dates would make looking back in time annoying).
That takes care of the first part of the formula.
That takes care of the second part.
And now put them together so they both must be TRUE:
Choose text strikethrough and remove the default cell coloring under Formatting style and you're done.
Add Apps Script
Now a little bonus feature - a checkbox to bring the user back from wherever they end up to today's date:

This is powered by a Google Apps Script file bound to the sheet.
It fires onOpen() and onEdit(e) of the today checkbox.
Here's the script:
function onOpen() {
goToToday();
}
function onEdit(e) {
if (e.oldValue !== "false" || e.value !== "TRUE") return;
const cell = e.range;
if (cell.rowStart !== 3) return;
if (cell.columnStart !== 9) return;
goToToday();
cell.setValue(false);
}
function goToToday() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getActiveSheet();
const dateRange = sheet.getRange('C3:F3');
const months = [
"January", "February", "March",
"April", "May", "June",
"July", "August", "September",
"October", "November", "December",
];
const today = new Date();
const month = months[today.getMonth()];
const year = today.getFullYear();
dateRange.setValues([[month, '', year, '']]);
}
onEdit(e) Function
The onEdit(e) function is a simple trigger that executes whenever a user changes a value in the spreadsheet.
Because it has the potential to run frequently, you want it to execute as quickly as possible when the change that happens isn't the one you're looking for.
That's why there are three checks (value, row, and column) to make sure the changed value was:
- a checkbox, and
- in the right location
By returning if these checks pass, the script is over quickly and doesn't slow down the sheet too much when a user makes an irrelevant change.
When the checks fail, the goToToday() function executes and the checkbox is unchecked.
Another bonus of using onEdit(e) is that it doesn't require a user to authorize the script so long as the code only affects the current spreadsheet.
goToToday() Function
This script is relatively straightforward.
It gets today's date and inserts the month name (from the months array) and year into the dropdowns above the calendar.
Remember that Date.prototype.getMonth() returns a zero-based value (January = 0).
This means it is ready to get values from the months array without issue.


FREE RESOURCE
Google Sheets Cheat Sheet
12 exclusive tips to make user-friendly sheets from today:

You'll get updates from me with an easy-to-find "unsubscribe" link.
