We were planning our Excel course schedules for 2009 when this Excel calendar template idea crossed my mind. I started to wonder: Why not plan my schedule in Excel? If you could also list all the holidays in one section of the worksheet and the calendar could show them in red, wouldn’t that be great? I started googling for an excel calendar and found many. Most of them are free. They weren’t really impressive because they have to be generated via a macro/vba. I feel like most Excel users won’t do well because they would have to understand how to activate the macro or install another program on their computers. If it is created manually without using any program, it will take a long time because we must first identify the 1st of each month manually and then do the manual sum for the rest of the days of the month. In addition, we have to know when to stop the calendar so that it does not go beyond the legitimate 28, 30 or 31 days.
As I continue to search through the list, I came across this perpetual calendar by John Walkenbach and was amazed at the way it was created. It is a perpetual excel calendar that shows the 12 months of any year. It uses only Excel formulas, which means you don’t need to know anything about macros, and it can be run in different versions of Excel, including Excel 2007. And right here, we’ll show you how it’s done.
Set up a cell that can be used for the year (eg C3)
Enter the following formula in cell C5 “=DATE(C3,1,1)”, where C3 refers to the calendar year.
Set the formula to present on the first day 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 day of the month. Use the day of week formula to identify the day of the week for the first month WEEKDAY(DATE(YEAR(C5), MONTH(C5), 1)
The weekday formula presents the week with Sun as the first day of the week and Sat as the seventh or last day of the week.
Minus one of the weekday formula, we will get Monday as 1 and Sun as zero. January 1, 2009 is a Thursday that coincides with the number 4. The Sunday before January 1, 2009 is actually December 28, 2008, which is 4 days before January 1, 2009. When we convert the number that we have in the previous step to negative, will match this date. The formula is =-(WEEKDAY(DATE(YEAR($C$5), MONTH($C$5), 1))-1).
The Sun in the upper right corner is 4 days earlier than January 1, 2009. Monday should be 3 days earlier and Tuesday 2 days earlier. So in this step we need to make the number increase over the week starting with -4. To do this you need to use an array formula which needs to be entered with square brackets (special case here). All the days of the month/week must be selected and the formula must be entered by pressing the 3 keyboard keys (Ctrl + Shift + Enter) together.
By using brackets {0,1,2,3,4,5,6} and selecting all 7 cells throughout the week, Excel will understand that we want to add 0 to Sunday, 1 to Monday, 2 to Tuesday, etc. The image below will allow you to understand how numbers can be changed with a formula.
In the second row/week of the month, the value must continue from the last value of the previous row. Since there are 7 days in a week, we know that the first value in the second row must be 7 more than the cell above it. We can add another array using semicolons (;) to indicate that we want the number to increase as the row increases. Must be enclosed in square brackets and multiplied 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)
-(DAY OF WEEK(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 above results to actual dates, we can add the date January 1, 2009 to the box. In this case, the first number will be December 28, 2008, December 29, 2008, and so on. And 32 will become February 1, 2009. We can enter the date using the date formula, Date(2009,1,1). And to display only the day of the month, we can format the cell using the custom format “d”.
To omit the dates of December 8 and the dates of February 9, 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 to the previous month or the next month. We can put a blank space (indicated by an open and closed inverted comma) in the cell (all cells). If the month of 2 dates is the same, then continue to perform the calculation given in the previous step. We end up with the following formula:
=IF(MONTH(DATE(YEAR(C5),MONTH(C5),1))MONTH(DATE(YEAR(C5),MONTH(C5),1)-(WEEKDAY(DATE(YEAR(C5), MES(C5) ),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),
“”,
DATE(YEAR(C5),MONTH(C5),1)-(DAY OF WEEK(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.