Select the range of month (in this example AH1:AH12
In the Cell link, select A1 (explanation, just under)
What is the cell link?
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
...
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
Create a list of years in a column. It's just the value of the year, not a date (have a look at the data in column AI)
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
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)
=DATE(A2+2016,A1,1)
Step 4: Extend the days
In C6, write the following formula
Copy the formula till the cell AF6
=B6+1
Step 5: Change the date format
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
Select B6:AF6
Activate the menu Home
Orientation
Rotate Text Up
Select columns B:AF
Right click a the column header
Set the column width to 2.5
Step 7: Add color and borders
Step 8: Add a dynamic title
=DATE(A2+2016,A1,1)
=DATE(A2+2016,A1+1,1)-1
="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")
Step 9: Highlight the weekends
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
Step 10: Highlight the public holidays
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)
Step 11: Hide the last columns with a macro
ALL THE EXPLANATIONS OF THE MACRO CODE HERE
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
Select the Combo Box
Right-click on it.
Choose Assign Macro
Select the name of the macro (Hide_Day) and click OK
0 Comments