Here’s a complete calendar in Excel using the following formula.
=IF(MONTH(DATE(YEAR(A1),MONTH(A1),1))<>MONTH(DATE(YEAR(A1),MONTH(A1),1)-
(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),””,
DATE(YEAR(A1),MONTH(A1),1)-(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))-1)+{0;1;2;3;4;5}*7
+{1,2,3,4,5,6,7}-1)
This is the first edition. Each month is on a separate worksheet. After revisiting this, I forgot how to do arrays in Excel. Follow theses steps in order for this code to work if you were to start from scratch.
- Click on cell A1 and type in a date like: 1/1/2010
- Click on cell A2 and highlight 7 columns to the right and 5 rows going down
- Paste in the code above in the formula bar for A2
- Hold down CTRL and Shift on the keyboard and press Enter
- Format the dates as you please
