Skip to content Skip to sidebar Skip to footer

Calendar In Excel 2018 Download

How to make automatic calendar in Excel

76 48894

Reading Time: 4 minutes

Step 1: Add a dropdown list

Insert a dropdown button in your worksheet

  1. Go to the menu File>Options
  2. Then Customize the Ribbon
  3. Check the developer option in the right pane
Add developer menu in the ribbon
Menu to insert a dropdown button for macros

Create a list of months

Automatic_Calendar_4
  1. Select your Combo Box object
  2. Right-click on it
  3. Select Format Control ...
Option for the dropdown button
  1. Select the Control tab (the last one)
  2. Select the range of month (in this example AH1:AH12
  3. 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.
Change the format of the dates

Step 6: Change the orientation of the text

  • Select B6:AF6
  • Activate the menu Home
  • Orientation
  • Rotate Text Up
Menu to rotate the wording
  • 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

  1. Select all your data with the first row of your calendar (range B6:AF13)
  2. Create a conditonal formatting rule (Home>Conditionnal Formating>New rules)
  3. Select Use a formula to determine which cells to format
  4. Write the formula=WEEKDAY(B$6,2)>5
  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

  1. Select all your data (cells B6:AF13)
  2. Create a conditional formatting rule (Home>Conditionnal Formating>New rules)
  3. Select Use a formula to determine which cells to format
  4. Write the formula=COUNTIF(Holidays!$B$2:$B$4,B$6)>0
  5. Change the background color (red)

Step 11: Hide the last columns with a macro

ALL THE EXPLANATIONS OF THE MACRO CODE HERE

  1. Press Alt + F11 to open the Visual Basic Editor
  2. From the Insert menu, choose Module
  3. 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

  1. Select the Combo Box
  2. Right-click on it.
  3. Choose Assign Macro
  4. Select the name of the macro (Hide_Day) and click OK

Step 13: Your calendar is ready

Automatic calendar finished

Source: https://www.excel-exercise.com/how-to-make-automatic-calendar-in-excel/

Posted by: ruebenikedae0194605.blogspot.com

Post a Comment for "Calendar In Excel 2018 Download"