How to Create a Dynamic Calendar Without Using Excel Macros
We were planning for our 2009 course schedules excel excel calendar template when this 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 on their Computers. If it is created manually without using any program, it is going to be time consumingbecause 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 macros 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.
How to Create a Dynamic Calendar Without Using Excel Macros
How to Create a Dynamic Calendar Without Using Excel Macros
How to Create a Dynamic Calendar Without Using Excel Macros
How to Create a Dynamic Calendar Without Using Excel Macros
Set up a cell which can be used for the year (e.g. C3)
Enter the following formulacell 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 January 1, 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. January 1, 2009 is a Thursday which coincides with the number 4. The Sunday before January 1, 09 is actually December 28, 08 which is 4 days before January 1, 09. When we convertthe number we have in the previous step to negative, it will coincide with this date. The formula is =-(WEEKDAY (DATE (YEAR ($ C $ 5), MONTH ($ C $ 5) .1)) -1).
The Sun on the top right hand corner is 4 days earlier than January 1, 2009. Monday should be 3 days earlier and Your 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 keyboard 3 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 1 to Sun, Mon, Tue, 2 toetc. 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 formulais
= 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 into real dates above, we can add the date January 1, 2009 into the box. In this case, the first number will become December 28, 08, December 29, 08, etc. And 32 will become February 1, 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".
To omit the Dec 08 Feb 09 dates and 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 dates shown in the active cell belongs either to the previous month or the following month. We can put a blank (denoted byopen and close inverted comma) into the cells (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.
How to Create a Dynamic Calendar Without Using Excel Macros