How to Create a Calendar in Google Sheets From Scratch - 56juqingba: How-to instructions you can trust

Spread the love

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.

Tip: Find out how you can sort by date in Google Sheets.

Content

Table of Contents

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.

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.

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.

Select the cell where you just entered the time, go to the toolbar, and choose the “More formats” button. Choose the “Time” format.

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.”

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.

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.

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.”

Select “Apply” when you finish. Your cells will be updated to the new time format.

When you’re finished, enter your items in the “Task” column next to the correct times.

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.

Start by entering the times of the day as described above, starting in the second row. Leave the first row blank for now.

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.

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.

You now have a weekly schedule where each day is divided into time increments. Enter your tasks, appointments, and meetings in the corresponding cells.

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.

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.

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:

=A1-WEEKDAY(A1)+1

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

=A1-WEEKDAY(A1,2)+1

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.”

=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.”

Select the cell, open the “Format” menu, then choose “Number -> Custom date and time.”

In the next window, remove each box at the top. Click the arrow on the right and select “Delete.”

To the right of the empty field, click the arrow and select “Day.”

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.

After you select “Apply,” the cell will update to display the day of the month.

Go to cell B3 and enter the following formula, which takes the date we just entered and adds one to it:

=A3+1

Go to cell C3 and enter this formula, which does the same as the one above.

=B3+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.

Go to cell A4 and enter this formula to add a day to the date from the previous line:

=G3+1

Go to cells B4 and C4 and enter these formulas, respectively, to add a day to the previous date:

=A4+1
=B4+1

Select cells B4 and C4, then drag the fill handle to the left to fill the cells.

Select the cells in row 4 that contain the formula and drag down to complete the month.

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.

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.

Right-click row 4 and choose “Insert 1 row above.”

Do the same for the remaining rows.

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.

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.

Each box row will update to the same size.

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

Select the cells in the first box row, open the “Text Wrapping” menu in the toolbar, and select the “Wrap” icon.

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.

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.

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.

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.

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.

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.

Click the “Merge cells” button in the toolbar to combine those cells and have the date cover them all.

Keep the cell selected, open the “Horizontal Align” menu in the toolbar, and pick the “Center” icon.

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.

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.

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.

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.

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.

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.

Select the entire calendar by dragging your cursor through all of the cells.

Open the “Borders” menu in the toolbar and choose the “All Borders” icon. Your calendar will immediately get a border.

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.

To copy the calendar to a new sheet, duplicate the sheet. Right-click the sheet name (tab) at the bottom and select “Duplicate.”

Change the month and year on the calendar on the new sheet and change the sheet name by double-clicking it.

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.”

Choose the cell where you want to paste it and press Ctrl + V (Windows) or Command + V (Mac).

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.

Subscribe to our newsletter!

Our latest tutorials delivered straight to your inbox

Sign up for all newsletters.
By signing up, you agree to our Privacy Policy and European users agree to the data transfer policy. We will not share your data and you can unsubscribe at any time. Subscribe

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.

Comments (1)