Excel Calendar Template

How to create a dynamic Excel Calendar without using macro

We were planning for our 2009 excel course schedules when this excel calendar template idea flashes across my head. I started asking myself: Why not plan my schedule in Excel? If I could also list down all the public holidays in one section of the worksheet and the calendar can actually displayed them in red, wouldn't that be great? I started to google for such an excel calendar and found many. Most of them are free. They were not really impressive because they have to be generated either through a macro/vba. I feel that it will not go well with most excel users because they would have to understand how to activate the macro or installing another program in their computers. If it is created manually without using any program, it is going to be time-consuming because by we need to identify the 1st of every month manually first and then doing manual summation to the rest of the days of the month. Furthermore, we have to know when to stop the calendar from going beyond the legitimate 28, 30 or 31 days.

As I continue to search through the list, I stumble on this perpetual calendar from John Walkenbach and was amazed by the way it was created. It is a perpetual excel calendar which displays the 12 months of any year. It uses only excel formulas, which means that you do not need to know anything about macro and it can be run across different versions of Excel including Excel 2007. And right here, we are going to show you how it is done.

Download the template by click on this excel calendar link

Set up a cell which can be used for the year (e.g. C3)

Enter the following formula in cell C5 "=DATE(C3,1,1)" where C3 refers to the year of the calendar.

Set the formula to present the first of the month. You can use the date formula. In our case, we can enter the formula as "=DATE(YEAR(C5),MONTH(C5),1)". C5 refers to 1 Jan 2009

Identify the day of the week for the 1st of the month. Use the weekday formula to identify the day of the week for the first of the motnh WEEKDAY(DATE(YEAR(C5),MONTH(C5),1)
The weekday formula presents the week with Sun as the 1st day of the week and Sat as the 7th or the last day of the week.

Minus one from the weekday formula, we will get Monday as 1 and sun as zero. 1 Jan 2009 is a Thursday which coincides with the number 4.

The Sun on the top right hand corner is 4 days earlier than 1 Jan 2009. Monday should be 3 days earlier and Tue 2 days earlier. Therefore, in this step, we need to make the number increase over the week starting with -4. To do this, you need to make use of array formula which must be entered with curly brackets (special case here). All the days in the month/week must be selected and the formula must be entered by pressing the 3 keyboard keys (Ctrl + Shift + Enter) together.

Using curly brackets {0,1,2,3,4,5,6} and selecting the 7 cells across the week, Excel will understand that we want to add 0 to Sun, 1 to Mon, 2 to Tue, etc. The picture below will allow you to understand how the numbers could be changed with one formula.

In the second row/week of the month, the value should continue from the last value in the previous row. Since there are 7 days in a week, we know that the first value in the second row should be 7 more than the cell above it. We can add another array using semi-colon (;) to indicate that we want the number to increase as the row increases. It should be presented within curly brackets and multiply by 7 - {0;1;2;3;4;5;6}*7. We should not add any number to the first row. Then the second row should add 7 to the number and add 14 to the third row and so on.

The formula is
=DATE(YEAR(C5),MONTH(C5),1)
-(WEEKDAY(DATE(YEAR(C5),MONTH(C5),1))-1)
+{0;1;2;3;4;5}*7
+{1,2,3,4,5,6,7}-1

To convert the results above into real dates, we can add the date 1 Jan 2009 into the box. In this case, the first number will become 28 Dec 08, 29 Dec 08, etc. And 32 will become 1 Feb 2009. We can put in the date using the date formula, Date(2009,1,1). And to display the day of the month only, we can format the cell with using the custom format "d". The table will be displayed as shown:

To omit the Dec 08 dates and Feb 09 dates, we can compare the month of the date with the month used in the first day of the month, etc. If they are different, it means that the date shown in the active cell belongs either to the previous month or the following month. We can put a blank(denoted by open and close inverted comma) into the cell (all the cells). If the month of 2 dates are the same, continue to perform the calculation given in the previous step. We ends up with the following formula:

=IF(MONTH(DATE(YEAR(C5),MONTH(C5),1))<>MONTH(DATE(YEAR(C5),MONTH(C5),1)-(WEEKDAY(DATE(YEAR(C5),MONTH(C5),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),
"",
DATE(YEAR(C5),MONTH(C5),1)-(WEEKDAY(DATE(YEAR(C5),MONTH(C5),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1) and completes the creation of the excel calendar template:
Using the formula version of conditional formatting, enter the vlookup and isna formulas to find whether there is a match in the list of public holidays. If there is, change the color of the cell to red. In the excel calendar template, a more complex vlookup formula is used to check if the public holiday falls on a Sunday. If it is a Sunday, it will color Monday red instead of Sunday.
Use the same conditional formula concept, enter the important dates and set the color of the cell if there is a match in the date. The second condition will be validated only if the first conditional formatting condition fails. This means that if your important date falls on a public holiday, it will be highlighted red instead of the color you specify here.