But in fact, with few formulas, 2 conditional formatting rules, 1 dropdown list and 5 lines of VBA code, you can create an automatic calendar. Follow each of these steps 🙂
Tutorial video to create an automatic calendar
If you want to have an overview of this article, you can watch the following video. To avoid to waste your time, I have deliberately increase the speed of the video. But don’t worry, all explanations are in the following steps.
Step 1: Add a dropdown list
Let’s start from this worksheet where we have nothing except our employees’ name for the beginning.
First, in A1, we will add a drop-down list in order to select the month. To do this, you must have the Developer menu in your ribbon. If you don’t, activate this option in the menu via File>Options>Customize Ribbon, and check Developer in the right pane.
Now, select Developer>Insert>Combo Box
Then left click and drag your mouse to create a dropdown objet in your worksheet like in this picture.
Now we need a list of month names somewhere else in our worksheet. In my case I need to place this list at least 32 columns away from my source data (maximum number of day per month + the column of the employees’ name), so I have used column AH (it’s the 34th column in Excel). Of course, if you don’t want this column visible for your users, you can hide it.
1. Select your Combo Box object
2. Right-click it.
3. Select Format Control …
The Format Control dialog will open.
- On the Control tab, go to the Input range text box and provide the location of the month list you created.
- In the Cell link field, you need to link to the cell under the Combo Box (A1)
The cell link is the location where Excel will place the index number of the item you select. For instance, if you select May, (the 5th item in the list,) the value in A1 will be 5 and if you select September the value in A1 will be 9. So why place it in A1? It’s a trick to hide the linked cell output under the actual Combo Box. 😉
So now, we are able to link the selected month name with a number in the cell A1. If you want, you can also create another drop-down list to select the year.
In the previous step, we created a drop down list to select a month but we did nothing for the year. For this example we just need to enter the year in a cell (in A2 in this example).
To do this, we have to use the formula DATE to calculate the first day of any month in fonction of the value in the cells A1 and A2. The value of the day in the formula must be equal to 1. As you can see on the pic, the formula in B6 depends on the result in A1 and A2
Step 3 – Change the date format and position in the cell
We have already done a great job 🙂 Now we are going to focus on the date format. We already have the year and the month in the upper left corner, so we don’t really need them in our daily column headers. To do this, we can change the format of the date in the row 6 to display only the day. We want to display the short name of the day and also the figure.
1. Select all your dates (from B6 to AF6)
2. Open the Format Cells dialog box (Ctrl+1 or Home>Cell format>More Number format)
3. Select the Custom category and enter ddd dd in the Type text box.
Now let’s change the orientation of our date:
1. Select B6:AF6
2. Go to Home>Orientation>Rotate Text Up
Then, adjust the size of the columns B:AF to get this presentation.
- Select columns B:AF
- Right click a the column header
- Set the column width to 2.5
Now, add some color and borders to your calendar. Pretty nice 🙂