Calendar

This example describes how to create a 2012 calendar in Excel (or 2013 calendar, 2014 calendar, etc). As always, if you are in a hurry, simply download the Excel file.

Note: the instructions below do not teach you how to format the worksheet. We assume that you know how to change font sizes, font styles, insert rows and columns, add borders, change background colors, etc.

This is what January 2012 looks like.

This is what January 2013 looks like.

The big question is: how do we tell Excel that January 2012 starts on a Sunday and January 2013 starts on a Tuesday?

1. First, to make our formulas easier to understand we have created the following names.

Explanation: Dates are stored as numbers in Excel and count the number of days since January 1st, 1900. The Date function returns the number that represents the date. The Weekday function returns the number of the day of the week for a date (ranging from 1 = Sunday to 7 = Saturday).

For example, January 1st, 2013 is the same as 41275. This date falls on Tuesday. In other words, =DATE(CalendarYear,1,1) equals 41275 and WEEKDAY(DATE(CalendarYear,1,1)) equals 3. As a result, JanOffset equals 41275 - 3 = 41272.

2. Let's take a look at the formula in cell B4.

Explanation: JanOffset+1 is nothing more than a date. In fact, it is (3-1) days earlier than January 1st, 2013. This formula checks if the year of this date equals CalendarYear and the month equals January (1). If true, it returns the date. If not, it returns an empty string ("").

3. Let's take a look at the formula in cell D4.

Explanation: JanOffset+3 is nothing more than a date. In fact, it is (3-3) days earlier than January 1st, 2013. This is the first day of the year! This formula checks if the year of this date equals CalendarYear and the month equals January (1). This is true! Excel returns the date.

4. We changed the format to "day format" to only display the day of the month.

Note: in a similar way you can create the formulas for the other days in January and the other months. This calendar tool lets you create a calendar template for every year you want! Just use the Spin Button to increase the calendar year. Feel free to download the Excel file.

Tip: Instead of creating your own calendar from scratch, you can create a calendar from a template.

Did you like this free Excel example? Show your appreciation, vote for us.

Thank you very much! We've got many more free Excel examples for you. Read on, learn, and enjoy!

Go to Top: Calendar    |    Go to Next Example: Invoice