How to Create a Calendar in Google Sheets From Scratch

How to Create a Calendar in Google Sheets From Scratch


You can create a calendar in Google Sheets in no time using a template. But if you prefer to make your calendar your own way and customize it, you can create one from scratch instead. We walk you through making a daily schedule, weekly schedule, and monthly calendar in Google Sheets. As a bonus, we also show how to make the monthly calendar dynamic so that the dates update when you change the month and year.

Create a Daily Schedule in Google Sheets

If you want to make a daily schedule in Google Sheets, you can do it in just minutes. Enter the times in your preferred increments, then add your appointments, meetings, or tasks.

  1. Add the “Time” and “Task” headers to the top of the first two columns and format the text the way you’d like using the “Font” section of the toolbar.
  1. In the first cell in the “Time” column, enter the time of day you want to begin. We are starting our days at 8 AM.
Time entered in Google Sheets
  1. Select the cell where you just entered the time, go to the toolbar, and choose the “More formats” button. Choose the “Time” format.
Time in the More Formats menu
  1. Enter the next time in the cell below. If you want to divide your day into 30-minute increments, enter 8:30. Select the “More formats” button again and choose “Time.”
Second time entered in Google Sheets
  1. Once you have two times entered, you can use the fill handle for the remaining cells in the “Time” column for your day. Select the cells with the times you entered and drag the fill handle down to fill the rest of the cells.
Times filled in Google Sheets
  1. If you prefer to format your times differently, select the cells containing the times. Choose “Format,” move to “Number,” and choose “Custom date and time” in the pop-out menu.
Custom Date and Time in the Format menu
  1. Use the boxes at the top to choose the format you want to use. You can add more by selecting the arrow to the right, or remove one by clicking its arrow and selecting “Delete.”
Custom time in Google Sheets
  1. Select “Apply” when you finish. Your cells will be updated to the new time format.
Daily schedule times updated
  1. When you’re finished, enter your items in the “Task” column next to the correct times.
Daily schedule in Google Sheets

Create a Weekly Schedule in Google Sheets

Creating a weekly schedule in Google Sheets is just as easy as a daily one. Plus, you can quickly convert a daily schedule into a weekly one with a few simple changes.

  1. Start by entering the times of the day as described above, starting in the second row. Leave the first row blank for now.
Times from a daily schedule entered in Google Sheets
  1. Go to the first cell in the second column, cell B1, and enter the first day of your week. For example, enter “Sunday” for a seven-day week or “Monday” for a five-day workweek.
Sunday entered for a weekly schedule
  1. Select the cell containing your day and drag to the right to fill the remaining cells in the row with the rest of the days.
Days filled for a weekly schedule
  1. You now have a weekly schedule where each day is divided into time increments. Enter your tasks, appointments, and meetings in the corresponding cells.
Weekly schedule in Google Sheets

Create a Dynamic Monthly Calendar in Google Sheets

Maybe you prefer to create a monthly calendar, whether for scheduling family occurrences or as a content calendar. You can set it up so that it’s dynamic. By entering the month and year, you’ll see the dates on the calendar update.

  1. Start by entering a month and year into cell A1 and the day of the week you want to start on, either “Sunday” or “Monday,” in cell A2. Then, drag the day of the week to the right as described above to fill in the remaining days.
Month, year, and days entered for a monthly calendar
  1. Enter the dates for the first week on the calendar using a formula with the WEEKDAY function to make the calendar dynamic. In cell A3, enter the following formula if your week starts on Sunday:

If you start your week on Monday, enter this formula instead:

If you’re interested in how the formula works, let’s break it down using the WEEKDAY function.

WEEKDAY(A1) says take the date in cell A1 and provide the day of the week that the first of the month falls on. For our “February” example, you can see that the first day of the month falls on the fourth day of the week, which is “Wednesday.”

Weekday formula in Google Sheets

=A1-WEEKDAY(A1)+1 subtracts that value from the same date and adds one. This provides us with the correct date for the start of the week on the monthly calendar, which is January 29.

For this example, we are using the first formula above and starting our weeks on Sunday. You’ll notice that the month and year appear, so you’ll need to format it as a “Day.”

Entire Weekday formula entered
  1. Select the cell, open the “Format” menu, then choose “Number -> Custom date and time.”
Custom Date and Time in the Format menu
  1. In the next window, remove each box at the top. Click the arrow on the right and select “Delete.”
Delete for a custom date and time format
  1. To the right of the empty field, click the arrow and select “Day.”
Day format option
  1. When the “Day” field appears, make sure it shows as “Day (5).” Use the arrow on the right to open the menu and choose this option if necessary.
Day 5 format
  1. After you select “Apply,” the cell will update to display the day of the month.
Date format updated
  1. Go to cell B3 and enter the following formula, which takes the date we just entered and adds one to it:
Monthly calendar formula plus 1
  1. Go to cell C3 and enter this formula, which does the same as the one above.
Monthly calendar formula plus 1 repeated
  1. Select the previous cells, B3 and C3, and use the fill handle to drag that formula to the right to fill the remaining week.
Plus 1 formula filled
  1. Go to cell A4 and enter this formula to add a day to the date from the previous line:
Plus 1 formula for the next row
  1. Go to cells B4 and C4 and enter these formulas, respectively, to add a day to the previous date:
Plus 1 formulas in the next row
  1. Select cells B4 and C4, then drag the fill handle to the left to fill the cells.
Plus 1 formulas filled in a row
  1. Select the cells in row 4 that contain the formula and drag down to complete the month.
All rows filled with the plus 1 formula
  1. Confirm that the dates are correct. You can change the month to see the dates update. For instance, you can enter “June 2023” or “6/2023,” press Enter or Return, and view the updated calendar.
Monthly calendar updated for June

Format the Monthly Calendar

Once you have the dates in your calendar, you’ll want to insert a row between each row of dates. This gives it a calendar appearance and allows you to type inside the date boxes.

  1. Right-click row 4 and choose “Insert 1 row above.”
Insert Row Above in the shortcut menu
  1. Do the same for the remaining rows.
Inserted rows
  1. Increase the height of those rows to have more space to enter items. Select each box row using a keyboard shortcut. Choose the first one, hold Ctrl (Windows) or Command (Mac), and choose each additional row.
Rows selected for a monthly calendar in Google Sheets
  1. Either drag down on one of the rows to increase the height, or right-click and select “Resize selected rows” to enter an exact size.
Resize Selected Rows in the shortcut menu
  1. Each box row will update to the same size.
Rows resized

You can also wrap the text and align it so that it displays properly.

  1. Select the cells in the first box row, open the “Text Wrapping” menu in the toolbar, and select the “Wrap” icon.
Wrap Text in the Google Sheets toolbar
  1. Any text you type in a box will remain in that box and wrap to a new line rather than carry over to the next blank box.
Wrapped versus not wrapped text in Google Sheets
  1. Align the text in those boxes so that it’s at the top instead of the bottom. Select the cells in the first box row, open the “Vertical Align” menu in the toolbar, and pick the “Top” icon.
Align Top in the Google Sheets toolbar
  1. Use the “Paint Format” tool to apply those same changes to the remaining box rows. Select a cell in the row you just formatted and click the “Paint Format” icon in the toolbar.
Paint Format in the Google Sheets toolbar
  1. Drag through the cells in the next box row. Those boxes will be formatted with the same text wrap and alignment. Follow the same process to format the additional box rows in your calendar.
Format painted in Google Sheets

Customize Your Calendar’s Appearance

In this next section, we walk through some optional formatting options for the appearance of your calendar.

Format the Headings

After you create the daily, weekly, or monthly calendar, you can adjust the headings at the top so that they stand out.

  1. For the monthly calendar, select the cell containing the month and year and drag through the cells to the right until you reach the last column in the calendar.
Month and year cell with cells on the right selected
  1. Click the “Merge cells” button in the toolbar to combine those cells and have the date cover them all.
Merge Cells in the Google Sheets toolbar
  1. Keep the cell selected, open the “Horizontal Align” menu in the toolbar, and pick the “Center” icon.
Align Center in the Google Sheets toolbar
  1. For the daily or weekly schedule, select the “Times and Tasks” headers as well as the days of the week and use the “Horizontal Align” menu to center them as well.
Align Center in the Google Sheets toolbar
  1. Use the “Font Size” section of the toolbar to increase the size of the font in any of the calendars. You can also make the font bold, italic, or give it a certain color.
Font in the Google Sheets toolbar

Format the Appearance of Weekdays and Dates

You can also add a fill color to the cells that contain the weekdays and the dates to the monthly calendar.

  1. Select the cells containing the days of the week or the dates. Use the “Fill Color” button in the toolbar to choose a color for the cells.
Days with a fill color
  1. Try using different shades of the same color for the days and dates. For instance, if you colored the weekdays dark blue, you could color the dates light blue.
Dates with a fill color

Use the Font section in the toolbar to format the text for the weekdays and dates, too, if you would like.

Add a Border

In one more formatting option, you can add a border to your calendar. This makes the days, times, dates, appointments, and entry boxes cleaner and clearer. Plus, you can easily add a border to the daily or weekly schedule along with the monthly calendar.

  1. Select the entire calendar by dragging your cursor through all of the cells.
Calendar selected to add a border
  1. Open the “Borders” menu in the toolbar and choose the “All Borders” icon. Your calendar will immediately get a border.
All Borders in the Google Sheets toolbar

Duplicate or Copy and Paste Your Calendar

If you want to copy your monthly calendar and paste it into another sheet or separate area on the same sheet, change the month and year on your subsequent calendars, and the dates will update.

  1. To copy the calendar to a new sheet, duplicate the sheet. Right-click the sheet name (tab) at the bottom and select “Duplicate.”
Duplicate in the tab menu
  1. Change the month and year on the calendar on the new sheet and change the sheet name by double-clicking it.
Monthly calendar duplicated
  1. Alternatively, if you want the calendar in the same sheet, select the entire calendar by dragging your cursor through all of the cells. Right-click and select “Copy.”
Copy in the shortcut menu
  1. Choose the cell where you want to paste it and press Ctrl + V (Windows) or Command + V (Mac).
Monthly calendar pasted

After you paste the monthly calendar, you may need to adjust the box row heights to the size you want, as described above.

Frequently Asked Questions

Is there a calendar template for Google Sheets?

Google Sheets offers a few calendar templates. Open the “Template Gallery” on the main Sheets screen and move down to the “Personal” section. You’ll see options for an “Annual Calendar” and “Schedule.” Simply select one to use it.

Additionally, view more options with this list of calendar templates for Google Sheets.

How do I format 24-hour time in Google Sheets?

When you select the cells containing your times and open the “Custom date and time formats” box described earlier, choose either the “13:25” or “13:25:59” option for 24-hour time.

How do I change the border color or width in Google Sheets?

You can pick the color and thickness of the border you apply to your calendar in Google Sheets in just a few clicks. Open the Borders menu in the toolbar. On the right side of the pop-up box, use the “Border Color” or “Border Style” drop-down boxes to pick a color or thickness.

Image credit: Pixabay. All screenshots by Sandy Writtenhouse.

Sandy Writtenhouse
Sandy Writtenhouse

With her BS in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She wanted to help others learn how technology can enrich business and personal lives and has shared her suggestions and how-tos across thousands of articles.

Subscribe to our newsletter!

Our latest tutorials delivered straight to your inbox


اكتشاف المزيد من موقع شبرون

اشترك للحصول على أحدث التدوينات المرسلة إلى بريدك الإلكتروني.

Comments

No comments yet. Why don’t you start the discussion?

اترك تعليقاً

لن يتم نشر عنوان بريدك الإلكتروني. الحقول الإلزامية مشار إليها بـ *