Daily Monthly Yearly Calendar Template

• Version
• 5'923 Downloads
• 160 KB File Size
• January 3, 2022 Updated
• 1 Number of comments
• ★★★★★

  ★★★★★
  Rating

Have you ever noticed how many times a day you reference the date? Checking your schedule, signing a document, or even remembering your age are all examples that refer to a date.

So, how do we remember all the dates in our busy lives? The most obvious is probably sitting on your desk or in your pocket. We have almost instant access to our calendars through our mobile phones – even while on vacation. According to a survey conducted for Asurion (a global tech company that provides mobile phone insurance) and reported on Studyfinds.org, the average American checks their phones 80 times every day while on vacation. Imagine how often we check our phones when we’re not trying to take a break!

But picking up the phone 80 times and even setting an occasional alarm doesn’t always remind us about scheduled events as well as a strategically placed, printable monthly calendar. Imagine you’ve signed up for a softball league. The alarm on your phone will help you get to your evening game on time. But, as you race out of the house in the morning for work, it’s the glance to the calendar placed by the front door that will keep you from leaving without your glove and bat.

This printable calendar template, available as a free download, can provide a visual cue to help with your daily schedule and time management tasks.

Quick Summary

This Daily-Monthly-Yearly Calendar template allows you to enter a daily event, that will be automatically color coded based on the category you assign.

This simple, color-coded view of daily assignments makes the schedule easy to read – providing that quick, visual check of your day.

Six preset categories – Anniversary, Holiday, Vacation, Birthday, Business, and Other – are easily customizable. You can change them to any category you need (see the ADVANCED section below to save time when customizing them). Once you enter an event and select a category, the day will auto-format with the assigned category color. The template will populate that color to the “Year” calendars (five versions) and “Month” calendars (two versions).

Template Contents

After you download the template, let’s take a look at some of the sample data and get familiar with the different worksheets and their layouts.

Setup

The first worksheet is the SETUP page (screenshot below). It includes Set Year, Category, Start Day, Weekend Marker (Daily Calendar), and Weekend Marker (Monthly and Yearly Calendar).

Changes made to this Setup page, also called a “Control Page,” affects the rest of the worksheet formats (I’ll cover the details of these fields in the INSTRUCTIONS section below).

In this preview, I’d like you to note that the “Vacation” category is yellow. We’ll use this category as our example to see how this SETUP page carries the color through to the daily list template, year calendar worksheets, and month calendar worksheets.

Daily Event List

Next, open up the DAILY worksheet (screenshot below), and you should see three columns: “Date” (Pre-populated), “Category” (Drop-down menu), and “Notes” (Custom text entry). This worksheet holds the template Events. These Events, also called the “data” or “dataset,” defines the dates and colors propagated to the yearly calendar view and the monthly calendar view.

Notice that the first entry, January 3, 2020, has the category “Vacation” and the color of that row is yellow (as defined in the SETUP worksheet).

Yearly Calendar View

Open YEARLY 1, the first worksheet with a year calendar view, and you’ll see the same date (January 3, 2020) formatted with yellow. (Note: The color key also prints on the bottom of each calendar).

The first worksheet is formatted with a light color theme. The following four yearly calendars include a blue-green layout, a light-green layout, a dark layout, and a light layout with an extra column for notes (see combined screenshot below).

Monthly Calendar View

The last two worksheets are monthly calendar views. The Monthly sheet is on the left with January 3 highlighted in yellow. Monthly 2 worksheet is on the right with a light-blue month heading and Times New Roman font. (Note: The font and font size of the yearly and monthly calendars are customizable – just like any standard cell).

How To Use the Daily Monthly Yearly Calendar Template

Now that we’ve taken a look at the formats and different calendar view options let’s move on to customizing the template for your specific needs.

Setting Up the Calendar

Open the SETUP worksheet and click on a cell to edit the contents based on the configuration options below.

1. Highlight and manually type the desired calendar year (all of the calendars will adjust their dates to the new year).
2. Highlight and type new category names (see the ADVANCED section below for more details on Changing Category Names).
3. Click on the cell, then choose from the drop-down menu to start the beginning of the week with either Sunday or Monday.
4. Click on the cell, then select from the drop-down menu to mark Saturday and Sunday in the daily calendar with a different color for quicker viewing.
5. Click on the cell, then select from the drop-down menu to mark Saturday and Sunday in the monthly and yearly calendars with different text color for quicker viewing.

The best way to get familiar with these SETUP options is to make a change on the SETUP page, then open the daily, yearly, and monthly worksheets and observe the changes. You can always go back to the SETUP worksheet and choose the “Undo” button or “Ctrl+Z” on the keyboard to erase the last change.

Using the Calendar

Once you’ve set up your calendar, it’s time to start using it. Open the “Daily” worksheet, highlight the sample data (cells C5:D12, C56:D56, and C174:D174) and hit delete. Now, start filling in your events. Enter the “Category” in column C by selecting from the drop-down menu and enter any custom “Notes” in column D by selecting the cell and typing.

Printing

The template designer formatted all worksheets on this template for portrait printing – except for the “Yearly 5” calendar. Due to the addition of the “Notes” section on the right, this calendar is landscape printable. This default setting makes it a great desktop calendar. Also, all worksheets are proportioned to print on letter-sized paper and work great as personal planner calendars. (Note: See the ADVANCED section below for Creating Half-Sheet Printable Planner Sheets).

Design

The design of this template is fairly simple:

1. The “Set Year” and “Start Day” are defined in the SETUP page and provide the basis for all the dates and days,
2. Calendar headers are formatted with preset – but changeable – colors and font,
3. Category colors are pre-set and built into the conditional formatting of each cell, and
4. Weekend marker colors (highlights and font) are preset but can be toggled on or off in the SETUP page.

The complexity in the design of this template – the part that will save you hours creating your own Excel calendar – is in the custom date formulas and the conditional formatting parameters.

Although I don’t want to deep dive into the details of creating formulas, you might better understand this template if we take a quick look. Open the “Yearly 1” worksheet and note that cell F6 displays the number three to represent the date (July 3). Now, select cell F6 and look at the formula bar (it’s above the column headings and also shown in the screenshot below). You should see a long string of numbers and text starting with an equal sign. This string of characters is the formula that calculates the date for that cell — in this case, number three.

The formula above uses several functions (small sub-programs built into Excel that you can combine within formulas to control the final output). Let’s take a deeper look into one of functions inside this formula (the selection bolded above):

This “DATE” function will return a number that Excel can use in calculations. It represents a specific date in time – called the “date-time code” – and is based on the number of days from January 1, 1900*. Here’s a table with some examples:

*(Note: A workbook can be set up to use a date system starting in the year 1904. The critical factor is that Excel will use the same basis for performing calculations within a workbook.)

Now, let me tie this back to our specific template. The “DATE” function uses the numbers in the parenthesis to determine the date-time code in the following order: Year, Month, and Day. So, in our example above:

YEAR = Setup!$C$4

MONTH = 1

DAY = 1

The representation of MONTH and DAY makes sense (MONTH = 1 → January; DAY = 1 → the 1st). But what about the year (YEAR = Setup!$C$4)? This “year” designation represents a location on the spreadsheet and looks at specific cell contents to pull its information. In this case, the cell reference is cell C4 on worksheet SETUP (Setup!$C$4). Take a look at that cell on our template. It refers to the “Set Year” that you defined in the control page of the worksheet.

That’s as deep as we’ll go into the design, but it lets you see part of the power behind this template. It’s important to not modify the formulas in the cells that have them. Otherwise, it might break the auto-population ability of your spreadsheet. But, if that happens, not all is lost; you can always download another one.

Advanced Tips

Want to level up your calendar skills? Here are some advanced tips.

Changing Category Names

Changing the categories names is straightforward. As described in the INSTRUCTIONS section above, select the cells and retype new category names. But, if you’ve already populated your “Daily” worksheet and used the old categories, you will need to go back and reselect the “new” categories for each date. Otherwise, your color won’t apply. So, if you complete your calendar and then decide you want to change the category names, don’t forget this step. Or, better yet, decide on the category names before filling in the calendar.

Creating Half-Sheet Printable Planner Sheets

If you use a personal planner, it’s very convenient to print out your own, customized planning pages. Printing To-Do lists, a typed reference list, or a pre-populated month calendar can add a lot of detail and organization to your planner. Plus, it saves a lot of rewriting or transferring information to planner pages. As described above in the PRINTING section, you can easily print these pages in their default orientation (portrait or landscape) on letter-sized paper (8.5” x 11”).

But, what if you use a smaller, half-sheet planner? Don’t despair. It’s still possible. This technique can be applied to most Excel templates – turning them into a printable planner template. (Note: I ran this printer setup on Microsoft Office 365. Your version might look different, but, if your printer is capable, it should be simple to locate the settings in an older version. I’ve been modifying Excel spreadsheets for half-sheet printing since the early 2000s and, although the locations have changed, the options are similar.)

First, you’ll need blank, half-sheet paper (5.5” x 8.5”) and a printer that can print those half-sheets (check with the online description of your printer or with your sales person). Next, if the printer doesn’t have half-sheet paper automatically setup, you’ll have to create a custom paper size. Let’s use the “Monthly” worksheet as our example. Open the sheet and navigate to “File” in the menu bar and then “Print.” Choose your printer and then click on the “Printer Properties” below the printer (see the screenshot). The paper size for my printer is located under a “Paper/Quality” tab and in the “Paper Options” section (see below). You’ll probably find the setting in a different place on your printer, but the concept is the same – you need to modify the paper size.

The default paper size is “Letter” (8.5” x 11”). Click on the “Custom” button under the “Paper sizes” drop-down menu, change the Name to Half-Sheet, the Width to 5.5, and the Length to 8.5 (make sure you select “Inches” for the unit). Then click “Save” and “OK” (see the screenshot below).

You’ve just created a “Half-Sheet” paper option that we can now use to print our small planner sheets. Hit one more “OK,” and we’re back to our Excel Print configuration page.

Once we create and select a different paper size, the margins won’t align correctly. The calendar pages will print with a full month and part of the next month on the same page. We need to fix this. So, the last step is to modify the bottom border of the “Monthly” worksheet. Click on the “Normal Margins” button, which opens the margins menu. Choose the “Custom Margins…” button (see screenshot below).

We need to use a little trial and error to determine the right bottom margin. In the “Custom Margins” window, change the “Bottom” margin to 2” (see the yellow highlight in the screenshot below).

After making the change, you should see the immediate effect in your opened preview window. The preview might still show that the calendars are not yet one month (or day or year) per page. You will need to modify the bottom margin based on your printer for the calendars to fit correctly – either increasing or decreasing it. (One of my printers required 2” and the other one 2.25” for the monthly calendar to be split successfully into 12 pages.)

I’ve included a proportionally scaled, digital copy of the printable monthly template from our example in both 8.5” x 11” and 5.5” x 8.5”. You can see there is extra space on the bottom to make the half-sheet work. This margin accounts for the slight proportion difference in letter paper (8.5” x 11” or .78 ratio) and the half-sheet paper (5.5” x 8.5” or .65 ratio).

This technique works with any of the worksheets and gives you a printable daily planner, monthly planner, and yearly planner pages. Following these instructions, and adding a dab of patience, you can create any page size that your printer will support. The only issue will be if the ratios of the worksheet work with the paper size that you need.