We were planning our Excel course programs for 2009 when this Excel calendar template idea crossed my mind. I started wondering: 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 display them in red, wouldn’t that be great? I started googling an Excel calendar and found many. Most of them are free. They weren’t really impressive because they have to be generated via a / vba macro. I feel like most Excel users will not 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 first day of each month manually and then do the manual addition of the rest of the days of the month. In addition, we have to know when to prevent the calendar from exceeding the legitimate 28, 30 or 31 days.
As I continue to browse the list, I stumble across this perpetual calendar by John Walkenbach and was in awe of 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 macro 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 a usable cell for the year (eg C3)
Enter the following formula in cell C5 “= DATE (C3,1,1)” where C3 refers to the calendar year.
Set up the formula to present 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 first day of the month. Use the weekday formula to identify the day of the week for the first day of the week (DATE (YEAR (C5), MONTH (C5), 1)
The weekday formula presents the week with the sun as the first day of the week and Saturday as the seventh or last day of the week.
Minus one of the formula for the day of the week, we will get Monday as 1 and the sun as zero. January 1, 2009 is a Thursday that matches 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 coincide with this date. The formula is = – (DAY OF THE WEEK (DATE (YEAR ($ C $ 5), MONTH ($ C $ 5), 1)) – 1).
The Sun in the upper right corner is 4 days before January 1, 2009. Monday must be 3 days before and Tuesday 2 days before. Therefore, in this step, we need to make the number increase during the week starting with -4. To do this you need to make use of an array formula that needs to be entered with braces (special case here). All days of the month / week must be selected and the formula must be entered by pressing the 3 keyboard keys (Ctrl + Shift + Enter) together.
Using braces {0,1,2,3,4,5,6} and selecting the 7 cells throughout the week, Excel will understand that we want to add 0 to Sunday, 1 to Monday, 2 to Tuesday, etc. The following image will help you 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 in 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. 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 must 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 THE 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 in the box. In this case, the first number will become December 28, 2008, December 29, 2008, and so on. And 32 will become February 1, 2009. We can put the date using the date formula, Date (2009,1,1). And to show only the day of the month, we can format the cell using the custom “d” format.
To omit the dates of December 08 and February 09, we can compare the month of the date with the month used on 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 opening and closing with a backward comma) in the cell (all cells). If the month of 2 dates is the same, continue with 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) – (DAY OF WEEK (DATE (YEAR (C5), MON (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 THE WEEK (DATE (YEAR (C5), MONTH (C5), 1)) – 1) + {0; 1; 2; 3; 4; 5} * 7 + {1,2,3,4,5,6,7} -1) and complete the creation of the Excel calendar template.