Step 1: A yearly calendar with Excel to create
First, it is important to create a simple calendar. To do this, you can use a simple formula that relieves you of some work.
- Enter the months from January to December in the first row, start at A1.
- In the field M1 of the year, and then: 2015
- Copy the following formula in the box A2: =IF(MONTH(DATE($M$1;COLUMN();ROW()-1))=COLUMN();DATE($M$1;COLUMN();ROW()-1);"")
- Drag the small Square in the lower right corner of cell A2, holding down the mouse button to the right to L1, then down to L32. (Here you can use the reread.)
Step 2: cell formatting on calendar customizing
The whole thing looks now quite ugly. To the right days, you must change the formatting.
- Select the range from A2 to L32 and click with the right mouse button
- Select "format cells"
- Search in "date" Format and the desired Format and confirm with "OK"
Setting the date format in Excel
Step 3: weekends and holidays stress
Using conditional formatting in Excel, you can now, the weekends highlight. To do this, use the following formula: "=WEEKDAY(A2;2)>5" (without the "")
- The holidays and personal days off you need to format manually. To do this, select all the appropriate days, by holding down the CTRL key and the days of clicking on it.
- Click with the right mouse button on one of the highlighted day and select "format cells".
- Select the desired formatting and click "OK".
Excel calendar with week ends
The manual works with all versions of Excel. It is important that you pay attention to the cell references. If you enter for example the year in a different cell, you must change the first formula accordingly. Tips on how to make all the Excel functions optimally exploit, you can here read.
