Creating a new calendar each month is a waste of time, but many people do as they think they need to rebuild it to get correct weekends and public holidays.
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.
Step 1: Add a dropdown list
Let's start by writing the employees' name in column A
Do you have the developer tab in your Ribbon? No, so
- Go to the menu File>Options
- Then Customize the Ribbon
- Check the developer option in the right pane
Now, select Developer>Insert>Combo Box
Then left click and drag your mouse to create a drop-down objet in your worksheet like in this picture.
Create a list of months
Now we need to create a list of month somewhere else in our worksheet (use the technique to fill series for that 😉)
In my case I have to create this list 32 columns away from my source data (maximum number of day per month + the column of the employees' name).
Now, connect the Combo Box with this list of month.
- Select your Combo Box object
- Right-click on it
- Select Format Control ...
The Format Control dialog will open.
- Select the Control tab (the last one)
- Select the range of month (in this example AH1:AH12
- In the Cell link, select A1 (explanation, just under)
What is the cell link?
The Cell link is the cell that will collect the value of the index of the element selected.
- If you select May, the 5th element in the list, the value in A1 will be 5
- And if you select September the value in A1 will be 9
Why choose A1? It's tricky 😉 In fact, the drop-down object is above the cell A1. Like that, the end-user will not see the result of the cell linked.
The location of the Cell link is crucial for the rest of the explanation. It has in impact on the formula DATE and with the macro.
Step 2: Another Drop-down list for the year
On the same way,
- Create a list of years in a column (it's just the value of the year, not a date)
- Insert a new drop-down list
- Link this new drop-down list to this range of year
- Select A2 as a Cell Link
Step 3: Create the first date in function of the drop down
Here, we are going to re-used the contains of A1 (month selected) and A2 (year selected) and put them in the DATE function. If you are not familiar with the DATE function, have a look at this article.
So, let's create the first day in function of the value of the drop down object.
- The year it's the value of the cell link A2 + 2016 (yes, the first year in the list is 2017, so the result in A2 will be 1 and not 2017)
- The month is just the value of A1 (easy)
- And the day is 1 (first day of the month)
In B6, the formula is
Step 4: Extend the days
This step is very easy.
- In C6, write the following formula
- Copy the formula till the cell AF6
Step 5: Change the date format
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 this information in our cells. What we need, it's just to display the days of our dates.
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.
- Select all your dates (from B6 to AF6)
- Open the Format Cells dialog box (Ctrl+1 or Home>Cell format>More Number format)
- Select the Custom category
- Enter ddd dd in the Type field.
Step 6: Change the orientation of the text
Now let's change the orientation of our date:
- Select B6:AF6
- Activate the menu Home
- 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
Step 7: Add color and borders
Now, add some color and borders to your calendar. Pretty nice 😍😍😍
Step 8: Add a dynamic title
Now, we are going to create a dynamic title with the first and last day of the month.
The formula for the first day of the month is
The formula for the last day of the month is
="Period from the "&TEXT(DATE(A2+2016,A1,1),"dd mmmm yyyy") &" to the "&TEXT(DATE(A2+2016,A1+1,1)-1,"dd mmmm yyyy")
And the result is:
Step 9: Highlight the weekends
Now, here is the most interesting part of the workbook. We are going to highlight automatically the week-end in the calendar
- Select all your data with the first row of your calendar (range B6:AF13)
- Create a conditonal formatting rule (Home>Conditionnal Formating>New rules)
- Select Use a formula to determine which cells to format
- Write the formula =WEEKDAY(B$6,2)>5
- Change the background color (orange in this example)
Very, very, VERY IMPORTANT ⚠⚠⚠ There is only one $ in the formula after the column (B) and before the row (6).
Step 10: Highlight the public holidays
For the public holidays, you need to create in a new worksheet, a list of the public holiday of your country
Then, repeat the same 5 previous steps but change the formula
- Select all your data (cells B6:AF13)
- Create a conditional formatting rule (Home>Conditionnal Formating>New rules)
- Select Use a formula to determine which cells to format
- Write the formula =COUNTIF(Holidays!$B$2:$B$4,B$6)>0
- Change the background color (red)
Here, we use the COUNTIF function. If one of the day is in the list of public holiday, the formula returns 1. So, we test if the formula returns a value greater than 0.
Step 11: Hide the last columns with a macro
To finish, we must create a macro.
The calendar is build with 31 days. So if we selected a month that hasn't 31 day, the calendar will not be correct. So we are going to create a macro that will hide the days not include in the selected month.
- Press Alt + F11 to open the Visual Basic Editor
- From the Insert menu, choose Module
- Copy Paste the following code in your module
Sub Hide_Day() Dim Num_Col As Long 'This instruction cleans the content of the cells in your calendar Range("B7:AF13").ClearContents For Num_Col = 30 To 32 ' Test if the month of the cells (row 6) is the same of the month selected (cell A1 or cells(1,1)) If Month(Cells(6, Num_Col)) >= Cells(1, 1) Then Columns(Num_Col).Hidden = True Else Columns(Num_Col).Hidden = False End If Next End Sub
Step 12: Link the macro to the drop down objets
To finish, we link the 2 drop down objects to this code.
- Select the Combo Box
- Right-click on it.
- Choose Assign Macro
- Select the name of the macro (Hide_Day) and click OK
Step 13: Your calendar is ready
And THAT'S IT 😎😍👍
Now, each time you select a month in your drop-down list, your calendar will automatically change the color of the weekend, holidays (providing your list is up to date, of course) and also hide columns as necessary.