subject: How To Create A Dynamic Microsoft Excel Calendar [print this page] Welcome to My Excel TutorialWelcome to My Excel Tutorial. Today we are going to learn how to make a dynamic calendar with Microsoft excel. You can use it to plan the activities day by day or only to see the days and dates in the year. You can change the year and the month and all the dates changes.
To make the calendar we are going to use the following excel functions:
DATE(), IF(), YEAR(), AND(), MONTH(), DATEVALUE() and WEEKDAY().
The first thing to do is to make an excel table, like a calendar, in the end of the post you can find an excel file with the calendar ready and a template to practice and learn how to build it.
Lets begin, we start with the title, to configure the title we are going to use 2 parameters, the user must fill, the year (cell K2) and the month (cell K3).
The title is the cell B1 and we are going to introduce the following excel formula: =DATE(K2,K3,1)
Here we use the DATE() function, that show us a date when we introduce the year and the month number, for the day we fill it with the number one. To visualize that date in the Month-Year format, we use the right click in the cell, then w ego to Format Cells, Number, Custom and in Type we introduce mmmm-yyyy.
The next step is find the first date in the calendar, with our configuration in the Excel worksheet (The first spot is a Sunday) our first calendar date will be the Sunday before the first date in the month selected.
This calculation we are going to make it in the cell K4 and we uses the following formula:
Now we are going to see the formula explanation. This formula has 2 parts. The first one DATEVALUE($K$3&"/1/"&$K$2) calculates the first day of the month (cell $K$3) and year (cell $K$2) selected. With the DATEVALUE function you obtain the number of the day (For example: 40245).
The second part calculates the number of the day in the week of the first day of the month and year selected. It is used the WEEKDAY function that shows the number of the day in the week, beginning with the number 1 for the Sunday and 7 to the Saturday. Inside the parenthesis you put the same excel formula of the first part that is explained in the last paragraph
Then, you subtract the second part to the first part and the number that you obtain is the date of Saturday before the first day of the month and year selected. Our excel calendar begins in a Sunday, so we have to sum one (+1) to obtain the date of the Sunday. This is the first date of our excel calendar.
For example, January 2013. The first day of the month (1-1-2013) is the number 41275, that day is a Tuesday, it means that is the number 3 of the week. When we subtract 3 to 41275 you obtain 41272 (29-12-2012), it is the Saturday before the first day of January 2013. We sum 1 to this and we obtain 41273 (30-12-2012). It means that the first week of January 2013 begins with the Sunday 30-12-2012.
After this we are going to calculate the dates in the excel calendar. The first thing to do is create a number series (You find it in the light brown cells in the excel template). In the second row we must fill the numbers from 0 to 6 above the days names. And in the left part of the calendar we put the 0, 7, 14, 21, 28 and 35 in the A4, A6, A8, A10, A12 and A14 respectively. This numbers are useful for create the excel formula and then copy it and paste it on all the cells of the excel calendar without making any changes.
This is a generic formula, you make it in the cell B4 and paste it in all the dates cells. Now lets see how it works.
The main parameter in the formula is: $K$4+B$2+$A4, this is the date for each day in the excel calendar. It is calculated taking the Sunday of the first week of the month and year selected and adding it for each cell the numbers that we placed before in the second row and in the A column. With this excel function our calendar is ready. But we can make a validation to take in count only the dates of the month.
To do that we use the IF()function, inside this function we make 2 validations, the first one is:
YEAR($K$4+B$2+$A4)=$K$2
That allows you to know if the year of the calculated date is equal to the year selected in the cell K2.
The second validation is: MONTH($K$4+B$2+$A4)=$K$3,
This validation is similar to the year validation, but reviewing if the month of the calculated date is the same of the month selected in cell K3. To ensure the accomplish of the two conditions we use the AND() function, if the two validations are good in the cell is showed the date value if isnt the cell shows nothing.
Now we have our excel calendar, you can hide the parameters, or change the format, the colors, the borders, etc. Also you can create a Sheet for each month and have the calendar for all the year.
