Excel: create calendar with working days 2015

A calendar in Excel, in which you can see your working days in 2015 at a glance - you can quickly program this yourself. In this practical tip, we explain how it's done.

Step 1: create an annual calendar with Excel

The first thing to do is to create a simple annual calendar. To do this, you can use a simple formula that does some work for you.

1. Enter the months from January to December in the first line, starting with A1.
2. The year then comes into field M1: 2015
3. Copy the following formula into the A2 field: = IF (MONTH (DATE ($ M $ 1; COLUMN (); ROW () - 1)) = COLUMN (); DATE ($ M $ 1; COLUMN (); ROW () -1);"")
4. While holding down the mouse button, drag the small square in the lower right corner of cell A2 to the right to L1, then down to L32. (Here you can read that again.)

Step 2: adapt cell formatting to calendar

The whole thing looks pretty ugly now. In order to display the correct days, you have to change the formatting.

1. Mark the range from A2 to L32 and right click on it
2. Select "Format cells"
3. Find the desired format under "Date" and confirm with "OK"

Step 3: highlight weekends and holidays

You can now highlight the weekends using conditional formatting in Excel. Use the following formula: "= WEEKDAY (A2; 2)> 5" (without the "")

1. You have to format the holidays and your personal days off manually. To do this, mark all relevant days by holding down CTRL and clicking on the days.
2. Right-click on one of the highlighted days and select "Format cells".
3. Select the desired formatting and click "OK".

The instructions work with all versions of Excel. It is important that you pay attention to the cell references. For example, if you enter the year in another cell, you have to change the first formula accordingly. Tips on how to make the best use of all Excel functions can be found here.