To create a dynamic calendar in Google Sheets, enter a date in a month-control cell, then use a SEQUENCE formula to generate the calendar dates automatically. Add conditional formatting and an event list if you want the sheet to flag appointments and show the selected month’s schedule. You’ll need Google Sheets in a web browser and a date that Sheets recognizes as a date.
What You’ll Need
- A Google account with access to Google Sheets in a web browser.
- A blank sheet or an area with room for the calendar, event list, and event log.
- Optional: a list of events with dates, which you can enter as you build the calendar.
Step-by-Step
-
Set up a clear workspace. Open a blank Google Sheet and use columns A:G for the calendar. Leave columns I:J for the event log and column L for a monthly event list. Keep the calendar cells unmerged: the formula will fill a 6-row by 7-column area, and merged cells in that area will block the result.
-
Choose a month-control cell. Type a date from the month you want to display into B1, such as June 1, 2026. The formula uses the month and year, so any valid date in June 2026 will select that month. To avoid ambiguity, check that the cell is recognized as a date: select B1 and choose Format > Number > Date if needed.
-
Add a heading that updates with the selected month. Put a heading in A1. With the selected month date in B1, enter this formula in A1:
=TEXT(B1,"mmmm yyyy")It displays the month and year, such as “June 2026.” Change B1 to another month and the heading updates with it. You can make A1 bold or increase its font size using the toolbar.
-
Enter the weekday labels. Type Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, and Saturday across A3:G3. This version starts weeks on Sunday. The calendar formula will place dates in the matching weekday columns, including dates from the previous or next month where needed to fill complete weeks.
-
Generate the dates automatically. Leave A4:G9 empty, select A4, and enter the following formula. It assumes the chosen month is in B1 and the weekday labels are in A3:G3:
=SEQUENCE(6,7,DATE(YEAR($B$1),MONTH($B$1),1)-WEEKDAY(DATE(YEAR($B$1),MONTH($B$1),1),1)+1,1)Press Enter. The formula fills the 42 calendar cells, beginning with the Sunday on or before the first day of the selected month. For June 2026, the first displayed date is May 31, 2026, because June 1 falls on a Monday. The dates recalculate when you change B1.
-
Show only the day number in each calendar cell. Select A4:G9, then choose Format > Number > Custom date and time. In the format controls, select or enter a day-of-month format, such as
d, and apply it. The cells still contain full date values; only their display changes. This matters because the event rules will compare actual dates, not just the visible day numbers. -
Fade dates outside the selected month. With A4:G9 selected, choose Format > Conditional formatting. In the conditional-formatting panel, set “Format cells if” to “Custom formula is.” With A4:G9 as the range, use:
=EOMONTH(A4,0)<>EOMONTH($B$1,0)Choose a muted text color and click Done. The formula checks each cell relative to A4, the top-left cell of the selected range. Dates belonging to the previous or next month will look different while remaining visible for a complete week layout.
-
Create an event log. Enter Date in I1 and Event in J1. Add each event on a separate row below, with the date in column I and its description in column J. For example, I2 might contain June 12, 2026, and J2 might contain “Project review.” Format the date column using Format > Number > Date. Avoid typing dates as plain text; Sheets must recognize them as dates for the highlighting and monthly list to work.
-
Highlight calendar days that have events. Select A4:G9 and open Format > Conditional formatting. Add another rule with “Custom formula is.” Because the event dates are in I2:I100 and the calendar begins at A4, enter:
=COUNTIF($I$2:$I$100,A4)>0Choose a noticeable fill color and click Done. Each calendar date matching at least one date in the event log will be highlighted. If you expect more than 99 events, extend the range in the formula and in any event-list formula below.
-
Show the selected month’s events in a separate list. Add a heading such as Monthly events in L3. With dates in I2:I100, event names in J2:J100, and the selected month in B1, enter this formula in L4:
=IFERROR(FILTER(TEXT($I$2:$I$100,"d mmm")&" - "&$J$2:$J$100,$I$2:$I$100>=DATE(YEAR($B$1),MONTH($B$1),1),$I$2:$I$100<EDATE(DATE(YEAR($B$1),MONTH($B$1),1),1)),"No events this month")The formula lists matching dates and event names, for example “12 Jun – Project review.” It updates when B1 changes. Keep the cells below L4 clear so the formula’s results can expand. If there are no events for that month, it displays “No events this month.”
-
Test the calendar and make it easy to reuse. Change B1 to a date in another month. Confirm that the heading, date grid, faded out-of-month dates, and event list all update. Add a test event for a date in that month and check that the matching calendar cell is highlighted. You can then replace the test event, save the sheet, and change B1 whenever you need a different month.
Common Mistakes
Typing a date that Sheets treats as text: A value that looks like a date may not be stored as one, especially if it was pasted from another source or uses a different regional format. Select the cell and use Format > Number > Date. If it still behaves like text, re-enter the date in a format recognized by your spreadsheet’s locale.
Putting content in the formula’s output area: The SEQUENCE formula needs A4:G9 free to expand. A note, space-containing formula, or merged cell inside that range can cause a “result was not automatically expanded” error. Clear the obstructing cells and unmerge any cells in the output range.
Using the wrong reference in conditional formatting: The custom rules are written for a range beginning at A4. If you move the calendar, update the first relative reference in each rule to the new top-left calendar cell. Keep the dollar signs on fixed references such as $B$1 and $I$2:$I$100.
Expecting event descriptions inside the date boxes: This setup highlights event days and displays event names in a separate list. It does not print the event descriptions inside each calendar cell. That keeps the date grid readable and makes multiple events on one date easier to handle.
Tips
Use a consistent event-log layout: one date and one event per row. If several events occur on the same day, use multiple rows with the same date; the calendar will still highlight that date, and the monthly list can show each event separately. Keep dates in column I and names in column J unless you also adjust the formulas.
For a Monday-start calendar, replace the two instances of the final 1 used as the WEEKDAY type in the date-grid formula with 2. In Google Sheets, WEEKDAY with type 2 treats Monday as 1. Also change the weekday labels to Monday through Sunday so the headings match the generated grid.
You can freeze the heading and weekday rows while scrolling: choose View > Freeze > Up to row 3. Consider using a different background for the weekday labels and a clear border around the calendar. Keep the month-control date visible so anyone using the sheet knows which month is selected.
If you share the sheet, explain that users should change B1 rather than overwrite the dates in A4:G9. You can also protect the formula cells with Data > Protect sheets and ranges, while leaving B1 and the event-log rows editable for people who need to update them.
These instructions use Google Sheets in a web browser. Excel has similar date and array formulas in current versions, but its menus differ: conditional formatting is under Home > Conditional Formatting in Excel, rather than Format > Conditional formatting. Do not assume that a Google Sheets menu path or formatting rule will transfer exactly.
Troubleshooting
The calendar formula shows a parse error. Check that the formula was copied with straight quotation marks where applicable, balanced parentheses, and commas between arguments. Spreadsheet locale settings can require semicolons instead of commas; if Sheets flags the separators, replace the argument-separating commas with the separators used by your locale.
The formula returns a #REF! error or will not expand. Clear cells A4:G9 and remove merged cells from that range. The formula in A4 produces the entire grid, so you should not enter separate values or formulas in the other 41 cells.
The calendar shows numbers that look like large serial values. The cells are dates, but their number format is not set to show a day. Select A4:G9 and use Format > Number > Custom date and time to choose a day-only display.
Events do not highlight or appear in the monthly list. Confirm that event dates are real date values, are in I2:I100, and fall within the month selected in B1. Check the conditional-formatting range and formula, and make sure the event-list output cells below L4 are empty. If an event date includes a time as well as a date, use a date-only value in the log so it matches the calendar date.
Frequently Asked Questions
Does the calendar update automatically when I change the month?
Yes. Enter a valid date from the month you want in B1. The heading, date grid, and monthly event list recalculate from that date.
Can I make the week start on Monday?
Yes. Use 2 as the WEEKDAY type in both places in the calendar formula and arrange the weekday labels from Monday through Sunday. The first date will then be the Monday on or before the first of the month.
Can I add more than one event to a day?
Yes. Add each event as a separate row with the same date in column I and a different name in column J. The calendar highlights the date, and the monthly list can display both entries.
Can I put event names directly in the calendar boxes?
This guide keeps names in a monthly list and uses color to identify busy dates. Putting multiple descriptions into a 42-cell grid requires a different layout and can make the cells crowded; the separate list is easier to scan and maintain.
Why do dates from the neighboring months appear?
The grid always shows six complete weeks, so it may include a few dates before or after the chosen month. The conditional-formatting rule fades those dates without removing them from the calendar.