Google Sheets Cheat Sheet

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:

complete month calendar based on user inputted values fed into a single formula
Free Calendar Sheet

Single Formula Calendar

You can input a month and year to see one formula produce a calendar for that month

Get Sheet

Let'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 List of items 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 List of items 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:

shows a blank calendar with day labels and user dropdowns above with some formatting applied to make it look nice

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:

=SEQUENCE(rows, [columns], [start], [step])
  • 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:

=SEQUENCE(6,7)

Will get you this (with the formula in the selected cell):

shows the output of the sequence function in the calendar area, the numbers 1 to 42

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:

=SEQUENCE(6,7,DATEVALUE(C3&E3))

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:

shows the output of the sequence and datevalue functions in the calendar area, the dates 1 July to 11 August

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:

=WEEKDAY(date, [type])
  • date = the date (obviously).
  • [type] = a number (1 (the default if omitted), 2, or 3) indicating how to number weekdays where:
    1. Sunday is 1 and Saturday is 7.
    2. Monday is 1 and Sunday is 7.
    3. 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:

  1. Monday
  2. Tuesday
  3. Wednesday
  4. Thursday
  5. Friday
  6. Saturday
  7. Sunday

Let's add this to your growing formula:

=SEQUENCE(6,7,DATEVALUE(C3&E3)-WEEKDAY(DATEVALUE(C3&E3),2))

To get a very calendar-looking output of:

shows the output of the sequence, datevalue, and weekday functions in the calendar area, the dates 27 June to 7 August, so they line up with their day labels

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:

=IF(logical_expression, value_if_true, value_if_false)

To say:

=IF(the date is not in the chosen month, don't show it, show it)

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:

=MONTH(SEQUENCE(6,7,DATEVALUE(C3&E3)-WEEKDAY(DATEVALUE(C3&E3),2)))<>MONTH(DATEVALUE(C3&E3))

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:

=ArrayFormula(IF(MONTH(SEQUENCE(6,7,DATEVALUE(C3&E3)-WEEKDAY(DATEVALUE(C3&E3),2)))<>MONTH(DATEVALUE(C3&E3)),,SEQUENCE(6,7,DATEVALUE(C3&E3)-WEEKDAY(DATEVALUE(C3&E3),2))))

To get:

shows the output of the arrayformula, if, sequence, datevalue, and weekday functions in the calendar area, the dates 1 July to 31 July with the others hidden

Now just change the number formatting to show only the day portion of the date by going to FormatNumberMore FormatsMore date and time formats and recreating this:

how to make a custom date and time format make a date show only the day part (no month or year)

Et voila, a calendar from a single formula:

the completed single formula google sheets calendar (without conditional formatting)

Add Conditional Formatting

Today Highlight

As a nice extra feature, you'll want to highlight today's date:

the single formula google sheets calendar with today's date highlighted using conditional formatting

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 FormatConditional formatting and in the sidebar under Format cells if... choose the Custom formula is option.

In 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:

=C5=TODAY()

Choose your desired highlight formatting under Formatting style and you're done.

Past Strikethrough

the single formula google sheets calendar with today's date highlighted and past dates with strikethrough text formatting using conditional formatting

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

=C5<TODAY()

That takes care of the first part of the formula.

=MONTH(C5)=MONTH(TODAY())

That takes care of the second part.

And now put them together so they both must be TRUE:

=AND(C5<TODAY(),MONTH(C5)=MONTH(TODAY()))

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:

shows a user executing the underlying google apps script by checking the today checkbox and the calendar automatically returning to the current month in response

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:

Dark theme
Copy code
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:

  1. a checkbox, and
  2. 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.


hand pointing emoji hand pointing emoji

FREE RESOURCE

Google Sheets Cheat Sheet

12 exclusive tips to make user-friendly sheets from today:

Google Sheets Cheat Sheet

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

Kieran Dixon started using spreadsheets in 2010. He leveled-up his skills working for banks and running his own business. Now he makes Google Sheets and Apps Script more approachable for anyone looking to streamline their business and life.

Want Better-Looking Google Sheets?

Google Sheets Cheat Sheet

Get my 12-tip cheat sheet that will make your spreadsheets more user-friendly.

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

🗙