A dynamic calendar in Google Sheets redraws itself when you pick a different year or month from a dropdown. You build the grid once, and from then on it works for any month you choose. This guide walks through the six steps behind the free template shown below, from the two dropdowns to the single SEQUENCE formula that fills all 42 date cells.
It is written for anyone who wants a printable monthly calendar, a planning grid for a team, or a calendar block inside a bigger tracker, without scripts or add-ons. The functions used are DATEVALUE, SEQUENCE and WEEKDAY, plus a custom number format and one conditional formatting rule.
🎥 Watch the Video Tutorial
Open the Free Dynamic Calendar Template
How the Dynamic Calendar in Google Sheets Is Laid Out
The template uses one sheet with a small, fixed layout. Knowing where each piece sits makes the formulas easier to follow:
| Cell / range | What it holds | Example value |
|---|---|---|
| E3 | Year dropdown | 2024 |
| E4 | Month dropdown | March |
| E5 | First date of the selected month (helper cell) | 1-Mar-2024 |
| B6:H6 | Day headers | Sun, Mon, Tue, Wed, Thu, Fri, Sat |
| B7:H12 | Date grid: 6 rows × 7 columns, filled by one formula in B7 | 25-Feb-2024 to 6-Apr-2024 |
Step 1: Set Up the Sheet
Open a new spreadsheet and rename the tab to Calendar. Merge B1:H1 (or A1:I1) for a title such as “Dynamic Calendar in Google Sheet”. In row 6, type the day names Sun to Sat in B6:H6. Leave B7:H12 empty, because the SEQUENCE formula needs all 42 cells free to spill into.
Step 2: Add the Year and Month Dropdowns
- Type the label Year in a cell to the left of E3 and Month next to E4.
- Select E3 and go to Data > Data validation > Add rule. Choose Dropdown and enter the years you need (the template uses 2020 to 2032).
- Select E4 and add a second dropdown with the month names January to December, spelled in full.
Full month names matter, because the next formula turns the text in E4 into a real date. If you want faster data entry on bigger sheets, see these tips for faster Google Sheets dropdowns.
Step 3: Calculate the First Date of the Month
In E5, combine “1-“, the month and the year, and convert the text to a date:
=DATEVALUE("1-"&$E$4&$E$3)With March and 2024 selected, the text becomes “1-March2024”, and DATEVALUE returns the date 1 March 2024 (serial number 45352). You can hide row 5 or set the font colour to match the background, since E5 is only a helper cell.
Step 4: Fill the Grid with SEQUENCE and WEEKDAY
Click B7 and enter one formula. It fills the whole 6 × 7 grid:
=SEQUENCE(6,7,E5-WEEKDAY(E5)+1)Syntax: what each part does
| Part | Meaning |
|---|---|
SEQUENCE(rows, columns, start, [step]) |
Returns a grid of consecutive numbers. Step defaults to 1. |
6 |
Six rows (weeks). Six covers every month layout, including a 31-day month that starts on Friday or Saturday. |
7 |
Seven columns, one per weekday. |
WEEKDAY(E5) |
Day number of the 1st of the month, with Sunday = 1 and Saturday = 7. |
E5-WEEKDAY(E5)+1 |
Steps back to the Sunday on or before the 1st, so every date lands under the right weekday heading. |
Worked example: March 2024
- E5 = 1 March 2024 = serial 45352, a Friday, so
WEEKDAY(E5)= 6. - Start = 45352 − 6 + 1 = 45347, which is Sunday 25 February 2024.
- The grid runs from 45347 in B7 to 45388 in H12 (Saturday 6 April 2024): 42 consecutive days.
At this stage the cells show raw serial numbers, exactly as in the screenshot below. Step 5 fixes that.
Step 5: Show Only the Day Number
- Select B7:H12.
- Go to Format > Number > Custom number format.
- Type DD and click Apply. The sample in the dialog changes to a two-digit day.
- Select the Sunday column (B7:B12) and set the font colour to red so weekends stand out.
Use D instead of DD if you prefer “1” over “01”.
Step 6: Hide Days from Other Months
The grid always starts on a Sunday, so it includes a few days from the previous and next month (25 to 29 February and 1 to 6 April for March 2024). One conditional formatting rule hides them:
- Select B7:H12 and open Format > Conditional formatting.
- Under Format cells if… choose Custom formula is.
- Enter the formula below.
- Under Formatting style, set the text colour to white (or the same colour as the cell fill), then click Done.
=MONTH(B7)<>MONTH($E$5)B7 is relative, so each cell checks its own date. $E$5 is absolute, so every cell compares against the selected month.
The finished calendar for March 2024 starts on Friday the 1st and ends on Sunday the 31st, with every Sunday in red:
Useful Variations
1. Start the week on Monday
Change the headers to Mon to Sun and use WEEKDAY type 2 (Monday = 1):
=SEQUENCE(6,7,E5-WEEKDAY(E5,2)+1)Move the red font to the last column (Sunday) as well.
2. Highlight today’s date
Add a second conditional formatting rule on B7:H12 with a fill colour:
=B7=TODAY()3. Shade both weekend days
For a Sunday-start grid, this rule catches Saturday and Sunday in one go:
=OR(WEEKDAY(B7)=1,WEEKDAY(B7)=7)4. Use a clearer date string
If you find “1-March2024” hard to read, add a separator. Both versions work in English locales:
=DATEVALUE("1-"&$E$4&"-"&$E$3)5. Mark holidays and events
Keep a list of holiday dates on another tab (for example Holidays!A2:A30) and add a rule:
=COUNTIF(Holidays!$A$2:$A$30,B7)>0For a full version with an events list, see our guide to a dynamic calendar with holidays and events in Google Sheets.
Common Errors and Fixes
| Problem | Likely cause | Fix |
|---|---|---|
| #REF! “Array result was not expanded” | Something is typed in B7:H12 other than B7 | Clear the rest of the grid so SEQUENCE can spill. |
| #VALUE! in E5 | Month text is abbreviated or misspelled, or your spreadsheet locale reads dates differently | Use full month names in the dropdown, or check File > Settings > Locale. |
| Cells show 45347, 45348… | The number format is still Automatic | Apply the custom format DD (Step 5). |
| Wrong days hidden, or none hidden | $ signs missing or placed on B7 | Use exactly =MONTH(B7)<>MONTH($E$5) with the range starting at B7. |
| Dates sit under the wrong weekday | Headers start on Monday but the formula uses default WEEKDAY | Match headers and WEEKDAY type (see Variation 1). |
Tips for a Cleaner Calendar
- Make the date cells tall and wide enough to add notes under each day if you plan to print it.
- Protect B7 (Data > Protect sheets and ranges) so nobody overwrites the formula by accident.
- Keep the helper cell E5 on the same sheet; hiding the row is safer than deleting it.
- Copy the tab 12 times and set a different month on each copy for a full-year workbook.
Want to build on these date skills? Our guides on the DATE, DAY and MONTH functions and on adding a date picker to Google Sheets pair well with this calendar. For ready-made layouts, take a look at the Holiday Schedule Calendar in Google Sheets.
If you want to go further with structured lessons on formulas like SEQUENCE, WEEKDAY and DATEVALUE, the Excel AI Mastery: 100 Formulas course on NextGenTemplates Academy covers them step by step. Most of the logic carries over directly to Google Sheets.
Frequently Asked Questions
Why does the calendar use 6 rows instead of 5?
A month can touch six different weeks. For example, a 31-day month that starts on a Saturday needs a sixth row for its last days. Six rows mean the grid never cuts off a date.
Can I make the calendar start on Monday?
Yes. Change the headers to Mon to Sun and use =SEQUENCE(6,7,E5-WEEKDAY(E5,2)+1). WEEKDAY type 2 counts Monday as 1.
Does the calendar handle leap years?
Yes. Because every cell is a real date, February 2028 will automatically show 29 days. No extra logic is needed.
Can I add events to specific days?
The date cells hold a formula, so you cannot type in them. Add a notes row under each week, or keep an events list on another tab and highlight matching dates with a COUNTIF conditional formatting rule.
How do I share the calendar with my team?
Click Share in the top-right corner and add people as viewers or editors. Viewers cannot change the dropdowns, so if each person needs to pick their own month, share a “make a copy” link instead.
Wrapping Up
With two dropdowns, one DATEVALUE helper, a single SEQUENCE formula, the DD number format and one conditional formatting rule, you have a calendar that rebuilds itself for any month and year. Open the template, change the year and month, and then try one variation, such as highlighting today or starting the week on Monday, to make it your own.
Get the Free Calendar Template
📅 Last updated: October 2026
