There are only 14 unique calendars; this instructable will teach you how to use Excel to make a list of calendars by year, so you can display your vintage calendars that are accurate for the current year. You can also use this technique to display a calendar from the future instead of a vintage calendar. The instructions laid out here work with any current version of Excel, although the instructions will be based on using Excel 2007.

This instructable will also demonstrate some time-saving Excel tips.

Step 1: The Fourteen Calendars

There are fourteen possible patterns for calendars; the list below shows all of the possible calendars.

Type 1: Year starts on Sunday, but it's not a leap year
Type 2: Year starts on Sunday, and it's a leap year
Type 3: Year starts on Monday, but it's not a leap year
Type 4: Year starts on Monday, and it's a leap year
Type 5: Year starts on Tuesday, but it's not a leap year
Type 6: Year starts on Tuesday, and it's a leap year
Type 7: Year starts on Wednesday, but it's not a leap year
Type 8: Year starts on Wednesday, and it's a leap year
Type 9: Year starts on Thursday, but it's not a leap year
Type 10: Year starts on Thursday, and it's a leap year
Type 11: Year starts on Friday, but it's not a leap year
Type 12: Year starts on Friday, and it's a leap year
Type 13: Year starts on Saturday, but it's not a leap year
Type 14: Year starts on Saturday, and it's a leap year

Step 2: Create the Column Headers for Your Excel Spreadsheet

In your Excel spreadsheet, label the cells as listed below:

Cell A1: Start
Cell B1: DOW
Cell C1: Day (Sun=1)
Cell D1: Leap?
Cell E1: Type
Cell F1: Year

Step 3: Build a List of New Year's Days

In cell A2, enter the date 1/1/1901. Excel has a bug that incorrectly assumes that 1900 was a leap year, so don't start before 1901. The rule for leap years is simple: The year has to be evenly divisible by 4, unless the year ends in 00 in which case the year also has to be evenly divisible by 400. Thus, 1900 was not a leap year, but 2000 was.

In cell A3, enter the date 1/1/1902. Hold down your left mouse, and select cells A2 and A3. You'll notice that in the lower-right corner of the selection there's a small black box; holding down your left mouse button, select the small black box and drag down (when your mouse is hovering over the correct place, it will turn into a 'plus' sign). As you drag down, you'll notice that a date will be displayed. Drag down until you get to 1/1/2036 and release the left mouse button. That's when I'll be able to retire, given the current market!

Step 4: Display the Day of the Week

In cell B2, enter =A2. This will show 1/1/1901. Then with the cursor still in cell B2 select the Format, Format Cells, select Custom, and in the Type: field enter dddd. This creates a custom number format for dates that shows the long day of the week.

Step 5: Display the Numeric Day of the Week

In cell C2, enter the formula =WEEKDAY(A2) and press enter. The WEEKDAY function returns a number for the day of the week. By entering the function this way, Sunday=1, Monday=2, et cetera. Since 1/1/1901 was a Tuesday, the equation returns a 3.

Step 6: Determine Whether a Given Year Was a Leap Year

In cell D2, we're going to enter a fairly complex formula to figure out whether the year is a leap year or not. Since the equation is fairly complex, I'll show it below and then explain how it does what it does. Here's the equation (enter it exactly as shown in cell D2):

=IF(OR(MOD(YEAR(A2),400)=0,AND(MOD(YEAR(A2),4)=0,MOD(YEAR(A2),100)<>0)),"Leap", "")

MOD is short for modulus, which is an integer that can be divided without remainder into the difference between two other integers. For example, 2 is a modulus of 5 and 9. The OR in the equation tests for three conditions, two of which (the AND) must occur together:

The year in cell A2, when divided by 400, is whole number (i.e. the remainder =0)

Here's the AND (both of the following conditions must occur):
The year in cell A2, when divided by 4, is whole number (i.e. the remainder =0)
The year in cell A2, when divided by 100, is not a whole number (i.e. the remainder <>0)

If the first of the three conditions is met OR both of the next two conditions are met, then the formula returns the word Leap. Otherwise, the formula results in a blank (that's the ""). When you press enter, you won't see anything because 1901 wasn't a leap year.

Step 7: Display the Year

Skip over cell E2 (we'll fill that in later). In cell F2, enter the equation =YEAR(A2). This will pull just the year out of the date. When you press enter the cell should say 1901.

Step 8: Copying All of the Formulas

Highlight cells B2 through F2. You'll notice that in the lower-right corner of the selection there's a small black box; holding down your left mouse button, select the small black box and drag down (when your mouse is hovering over the correct place, it will turn into a 'plus' sign). As you drag down, you'll notice that a date will be displayed. Drag down until you get to 1/1/2036 and release the left mouse button. When you release the mouse button, all of the formulas that you wrote will be copied down to the bottom of the spreadsheet. You see some years labeled Leap. Those are the leap years.

Step 9: Converting the Formulas to Values

In this step, we'll convert all of our formulas to values.

Move your cursor to cell A2. Holding down the Shift key, tap the End key then tap the Down arrow. Don't release the Shift key. You should now have all of the dates (down to 1/1/2036) highlighted. While still holding the Shift key down, press the Right arrow five times. You should now have cells A2 through F137 highlighted. Right-mouse inside the highlighted area. The border should change to a moving dotted line. Select Copy from the shortcut menu. Then, right mouse again, but this time select Paste Special, click on the Values radio button, and select OK. Check any cell that had a formula (like C2 or F2); the formula should have been replaced by the formula's result.

Step 10: Sort the Year Types Together

Move your cursor to cell A2. Select Sort & Filter, then select Custom Sort. Sort by the Day (Sun=1) and Leap? columns. Click OK. Your data will be sorted so all of the Type 1 (week starts on Sunday, not a leap year) are together, et cetera.

Step 11: Identify Years by Year Types

Move your cursor to cell E2. Manually enter the numbers for the calendar type from Step 1 down through all of the dates in the list. The list is repeated below, too.

Type 1: Year starts on Sunday, but it's not a leap year
Type 2: Year starts on Sunday, and it's a leap year
Type 3: Year starts on Monday, but it's not a leap year
Type 4: Year starts on Monday, and it's a leap year
Type 5: Year starts on Tuesday, but it's not a leap year
Type 6: Year starts on Tuesday, and it's a leap year
Type 7: Year starts on Wednesday, but it's not a leap year
Type 8: Year starts on Wednesday, and it's a leap year
Type 9: Year starts on Thursday, but it's not a leap year
Type 10: Year starts on Thursday, and it's a leap year
Type 11: Year starts on Friday, but it's not a leap year
Type 12: Year starts on Friday, and it's a leap year
Type 13: Year starts on Saturday, but it's not a leap year
Type 14: Year starts on Saturday, and it's a leap year

Step 12: Find a Calendar

To find a specific year, press CTRL F on your keyboard, and search for the year. For instance, 2009 is calendar type 5. That means you can use a calendar from any of the years of the same type (1903, 1914, 1925, 1931, 1942, 1953, 1959, 1970, 1981,1987, 1998, 2009, 2015, or 2026) and it will be identical to this year's calendar.

Oddly enough, this is a milestone birthday year for me, and this year's calendar is identical to the year in which I was born. I leave it as an exercise for the student to determine how old I am. Enjoy!