How To Make A Calendar In Google Sheets

Just Build One Instead of Downloading Junk

Most people who ask for a calendar template end up downloading one filled with broken formulas, hidden rows, and colors that make their eyes hurt. I stopped recommending third-party templates around 2019. Building your own takes about eight minutes and you actually understand how it works when something breaks three months later. Open a blank sheet. In cell A1 type the start date of the month you want — for example 9/1/2024 for September 2024. Press Enter. In A2, type this formula: =IF(A1+7>"&TEXT(EOMONTH(A1,0),"mm/dd/yyyy"),"",(A1+7)-EOMONTH(A1,0)+1)

That formula pulls each date and wraps correctly when the month changes. Now drag the fill handle from A1 down to row 36. You should have a single column of dates covering roughly six weeks, which is enough to span any month. Here is where most people mess up. Create a header row above your data and label columns Sun through Sat. Then use this arrangement instead. Put your dates in column A starting at row 3 with just the number formula =A1+1 dragged down. Column B gets =A3, column C gets =B3+7, and you keep going right until column G has =F3+7. This gives you the standard seven-day week layout. Fill down to row 9 or 10. The trick nobody explains well is that the dates only look right if your sheet's regional settings match US date formatting. If your numbers show as something like 2024-09-01 instead of 9/1/2024, go to File > Settings and change your locale. Otherwise the calendar looks like garbage and you will waste an hour wondering why.

Conditional Formatting That Actually Works

Highlight your entire date grid. Go to Format > Conditional formatting. Add a rule where the custom formula is =WEEKDAY(A3,2)>5 and set the fill color to a light gray. This shades weekends without manually coloring every cell. Do the same for holidays if you want a second color layer. I keep a separate sheet tab called "Holidays" with date values and reference that in a second conditional rule. It takes about two minutes to set up and saves you from picking through twelve individual color cells later. I once spent forty-five minutes debugging a calendar that kept showing the wrong month because a coworker had changed the sheet locale to UK format while I was building it. The formulas were fine. The display was just interpreting 9/1/2024 as January 9th instead of September 1st. Never trust a shared template without checking Settings first.

Print Setup And The Edge Case You Will Hit

Go to File > Page setup and set it to Landscape. Check the print preview before you commit. By default Google Sheets will cut off the rightmost columns if your calendar runs into a column past H. Resize columns so each day cell is roughly 1.5 inches wide. Set the print range to your exact calendar area rather than letting it default to the whole sheet, which includes your helper columns and holiday reference data. One specific problem I run into regularly: when you merge the weekend cells for visual appeal, the formulas break because merged cells cannot hold independent formulas. I stopped merging cells entirely. Instead I use a single cell for the date number and format the adjacent weekend cells with a background color and a border to create the appearance of a merged block. It looks identical in print but the data stays intact.

Limitations You Need To Know

This approach gives you a visual calendar. It does not track events, send reminders, or sync across devices. If you need a calendar where you can click a day and add an appointment that pops up on your phone, use Google Calendar. The Sheets method is useful when you need a printable monthly overview, a shared planning document where multiple people can type notes into cells, or a static reference sheet embedded in a larger report. It is not a replacement for an actual scheduling tool. Also, if you ever copy this calendar to another sheet or share it with someone who has a different default date format, the display can shift. Always lock your date format by selecting the date cells, going to Format > Number, and choosing a custom format of M/D/YYYY. This prevents the cells from reformating when the sheet is opened on a different regional setup. The whole process from blank sheet to finished printed calendar usually takes around ten minutes if you know what you are doing and about twenty if you are figuring it out for the first time. Once built, you can reuse the same template next month by changing the start date in the top-left cell and adjusting the conditional formatting ranges if the month spills into a different number of weeks.