How to Create a Dynamic Calendar in Google Sheets | Sunday, Monday & Dropdown Version, by Spreadsheet Mode on YouTube. 9 min 18 s.
- Video length
- 9 min 18 s
- Time to do it
- 20 minutes
- Difficulty
- Medium
- Steps
- 8
What you need
Steps
Open a blank Google Sheet and type a real date into cell B1, such as 1/1/2026. This one cell drives the whole calendar, so it must be a date and not text.
Click cell B3 and paste =SEQUENCE(6,7,DATE(YEAR(B1),MONTH(B1),1)-WEEKDAY(DATE(YEAR(B1),MONTH(B1),1))). Press Enter to fill a 6 row by 7 column block of dates.
Type Sun, Mon, Tue, Wed, Thu, Fri and Sat across the row directly above the grid. Widen the columns and set the date cells to a short date format so the numbers fit.
Select the grid and open Format, then Conditional formatting. Add a rule with the custom formula =MONTH(B3)MONTH($B$1) and set the text colour to white to hide dates from other months.
Add a second conditional formatting rule on the same range with =B3=TODAY() and choose a fill colour. Today's date now stands out whenever the sheet recalculates.
For a Monday start, build a second grid and use the same SEQUENCE formula with,2 added inside WEEKDAY so weeks begin on Monday. Change the header row to Mon through Sun.
In the dropdown version, select cell A5 and use Data, then Data validation to add a list with Sunday and Monday. Type the headers with =IF(A5="Monday",{"Mon","Tue","Wed","Thu","Fri","Sat","Sun"},{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"}).
Build the dynamic grid with =SEQUENCE(6,7,DATE(YEAR(A2),MONTH(A2),1)-WEEKDAY(DATE(YEAR(A2),MONTH(A2),1),IF(A5="Monday",2,1))+1). Pick Sunday or Monday in the dropdown and the whole calendar flips.
Tips and common mistakes
If the grid shows an error, check that your month cell is a real date, because DATE(YEAR(B1),MONTH(B1),1) fails on plain text.
Some regions use semicolons instead of commas in formulas, so type =SEQUENCE(6;7;...) if commas throw an error.
A 6 by 7 grid always holds 42 cells, so short months leave a few rows of next-month dates at the bottom.
Keep the header row and the date grid aligned, or the conditional formatting rule will colour the wrong cells.
Questions
Why does my calendar show a #NAME? error?
The SEQUENCE function name or the argument separators are wrong for your spreadsheet locale. Try semicolons instead of commas, and check the spelling of SEQUENCE.
Can I change the month without rebuilding the calendar?
Yes. Edit the date in B1 or A2 and the grid updates automatically. Only the month and year matter, so any day in that month works.
Why do dates from the next month appear in my grid?
The formula always fills 42 cells, so it spills into the next month. Use the MONTH conditional formatting rule to turn those dates white.
This guide was drafted from the video's details and general task knowledge. It has not been checked against the full video. Check the video and relevant product instructions before following a procedure. Report a correction.
