Calendar

There are several ways to create Excel calendar. Also there are multiple templates that you can use for that purpose. The goal of this „recipe“ is helping you to understand practical application of some important date&time functions, and creating a calendar is an ideal opportunity to do so. When you learn how to generate dates and positions they should fit in you can use calendar for planning activities or tracking project realization.

Every complex task is best solved when we break it into several small ones. In this example, our goal is to enable easy month entry and to get answers on following questions:

  • Which month did we choose?
  • In which day of the week falls first date in month?
  • How many days contains the month we have chosen?
  • How to calculate positions of days within a calendar?

For start, we should rename current spreadsheet to Calendar. Then, we should add another spreadsheet and name it Codebook. Here we’ll create a table with two columns. First should contain names of the months and second numbers that represent them.00152E-1

In cell A1 of spreadsheet Calendar we’ll add caption MONTH. Right of it is a place we’ll use to make a choice. For that purpose it is best to merge two adjacent cells (A2:A3). We can also apply some formatting to the cells (font color, background color, frames etc.). Then we should start Data Validation, an option that lies within Data ribbon. The dialog box will appear. Here we choose to pick values from a list that should be populated from first column of a table that we previously created within Codebook spreadsheet. If we did all properly we’ll be able to pick a month from the list.

00152E-2

After that we should create a table below which will represent a calendar. For better appearance we should add some formatting as well. In table header we add names of the days, and above it numbers from 1 to 7. Also, right from the table, we should add a column with numbers from 0 to 5, that represent weeks.

To determine a number of the month we enter following formula within E1:
=VLOOKUP(B1,Codebook!A1:B12,2,FALSE)

Next, to determine a day of the week for first day of the month we should enter a formula within cell F1:

=WEEKDAY(DATE(YEAR(TODAY()),E1,1),2)

At the end we enter a formula within G1 that determines a number of days in a month we have chosen.

=DATE(YEAR(TODAY()),E1+1,1)-DATE(YEAR(TODAY()),E1,1)

In second and third formula we have used TODAY() function which returns a date within current year. Instead of that we could have used a cell address, to enter a year manually.

00152E-3

At the end we should create formulas necessary to calculate positions of days within a calendar. In this example we make two formulas: first for prime week, and second for following weeks. Goal of the first formula is to check difference between day of the week and first day in a month. If it is a positive number it is written within a cell. Otherwise the blank value is written. Here’s how it should look like:

=IF((A3-$F$1)>=0,(A3-$F$1)+1,””)

After we entered formula in cell A5 we have to copy it to all other cells within the same row. Then, in cell A6 we should enter a formula that, depending of the week number, adds certain amount of days:

=IF((A$3-$F$1+1+$H6*7)<=$G$1,(A$3-$F$1+1+$H6*7),””)

This formula should be copied to the remaining cells in a table.

00152E-4

For better appearance it would be suitable to hide numbers of days, weeks and also content of cells E1, F1 and G1. The most appropriate way is to change the font color of these cells to color of cell background. The result of our work is a calendar table that automatically changes each time we pick up another month from the list.

00152E-5