Calendar In Excel 2018 Download

How to make automatic calendar in Excel

15/09/2021 15/09/2021 76 48894

Reading Time: 4 minutes

Step 1: Add a dropdown list

Insert a dropdown button in your worksheet

Go to the menu File>Options
Then Customize the Ribbon
Check the developer option in the right pane

Menu to insert a dropdown button for macros

Create a list of months

Select your Combo Box object
Right-click on it
Select Format Control ...

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?

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)) &amp;amp;amp;amp;amp;amp;amp;gt;= 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
            
                                      
            Step 13: Your calendar is ready