How to create a calendar in Excel

This example describes how to create a 2015 (or 2017, 2018, etc.) calendar in Excel.

Note: The instructions below will not teach you how to format a document. We assume that you already know how to change font size and style, insert rows and columns, add borders, change background color, and more.

Here’s what January 2015 looks like:

Important point: How could we explain to Excel that January 2015 starts on a Thursday?

1. To make our formulas easier to understand, we will create the following names:
   • CalendarYear – calendar year. The value will be stored in the cell K5.
   • JanOffset – the name of the formula, which is presented below:

   =DATE(CalendarYear,1,1)-WEEKDAY(DATE(CalendarYear,1,1))

   =ДАТА(CalendarYear;1;1)-ДЕНЬНЕД(ДАТА(CalendarYear;1;1))

   Explanation:

   • Dates in Excel are stored as numbers.
   • The program counts the number of days that have passed since January 0, 1900.
   • Function DATE (DATE) returns a value representing a date.
   • Function DATE (DATE) takes three arguments: year, month, and day.
   • Function WEEKDAY (WEEKDAY) returns a number between 1 (Sunday) and 7 (Saturday) representing the day of the week.

   For example, January 1, 2015 is the same as 42005. This date falls on a Thursday. In other words, =DATE(CalendarYear;1;1) equals 42005, and DAY(DATA(CalendarYear;1;1)) equals 5. As a result, the JanOffset variable is equal to 42005-5=42000.

Translator’s Note: In some countries of the world, in particular in the USA, it is customary to consider Sunday as the first day of the week. So here is the formula JanOffset calculates a date that falls on the last Saturday of the December that precedes the January we are considering. In this example, this date is the starting point.

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

   =IF(AND(YEAR(JanOffset+1)=CalendarYear,MONTH(JanOffset+1)=1),JanOffset+1,"")

   =ЕСЛИ(И(ГОД(JanOffset+1)=CalendarYear;МЕСЯЦ(JanOffset+1)=1);JanOffset+1;"")

   

   Explanation: Expression JanOffset+1 is the date that is (5-1) days after January 1, 2015. This formula checks if the year of this date is equal to the variable CalendarYear and whether the month of that date is January (1). If so, the formula returns the date, and if not, an empty string (“”).

3. Let’s take a look at the formula in a cell F4.

   =IF(AND(YEAR(JanOffset+5)=CalendarYear,MONTH(JanOffset+5)=1),JanOffset+5,"")

   =ЕСЛИ(И(ГОД(JanOffset+5)=CalendarYear;МЕСЯЦ(JanOffset+5)=1);JanOffset+5;"")

   

   Explanation: Expression JanOffset+5 is the date that is (5-5) days away from January 1, 2015. This is the first day of the year! The formula in the figure checks if the year of this date is equal to the variable CalendarYear and whether the month of that date is January (1). The result is positive! Excel returns the date.

4. We have changed the format type to “day format” to display only the day of the month.

Note: Similarly, you can create formulas for other days in January and other months. This tool will allow you to create a calendar for any year! Just use the counter to select the year to get a calendar for another year.