How to create a dynamic Excel Calendar without using macro
We were planning for our
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 timeconsuming because
we need to identify the 1st of every month manually first and then doing manual summation
to populate the rest of the days of the month. Furthermore, we have to manually 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 Microsoft Excel including
Excel 2007.
And right here, we are going to show you how it is done.
 Download the excel calendar 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 month WEEKDAY(DATE(YEAR(C5),MONTH(C5),1)
The weekday formula presents the week with Sunday as the 1st day of the
week and Saturday 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 Sunday before 1 Jan 09 is
actually 28 Dec 08 which is 4 days before 1 Jan 09. When we convert the number
we have in the previous step to negative, it will coincide with this date.
And the results for using the formula
=(WEEKDAY(DATE(YEAR($C$5),MONTH($C$5),1))1) are
 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 the 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 semicolon (;) 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.
You can request for a FREE copy of the
excel calendar
if you wish to use it right away!
•

New! Comments
Have your say about what you just read! Leave me a comment in the box below.