Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in


How to create a dynamic monthly calendar in Excel?

You may need to create a dynamic monthly calendar in Excel in some purpose. When changing the month, all dates in the calendar will be adjusted automatically based on the changed month. This article will show you method to create a dynamic monthly calendar in Excel in details.

Create a dynamic monthly calendar in Excel

Excel Productivity Tools

Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial

Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately. 60-day Unlimited Free Trial

arrow blue right bubble Create a dynamic monthly calendar in Excel

Please do as follows to create a dynamic monthly calendar in Excel.

1. You need to create a Form Controls Combo Box in advance. Click Developer > Insert > Combo Box (Form Control). See screenshot:

2. Then draw a Combo Box in cell A1.

3. Create a list with all month names. As below screenshot shown, here I create this month name list in range AH1:AH12.

4. Right click the Combo Box, and click Format Control from the right-clicking menu. See screenshot:

5. In the Format Control dialog box, and under the Control tab, select the range contains the month names you have created in step 3 in the Input range box, and in the Cell link box, select A1, then change the number in the Drop down line box to 12, and finally click the OK button. See screenshot:

6. Select a blank cell for displaying the start date of month (here I select cell B6), then enter formula =DATE(A2,A1,1) into the formula bar, and press the Enter key.

Note: In the formula, A2 is the cell contains the certain year, and A1 is the Combo Box contains all months of a year. When selecting March from the Combo Box and entering 2016 in cell A2, the date in cell B6 will turn into 2016/3/1. See above screenshot:

7. Select the right cell of B6, enter formula =B6+1 into the Formula Bar and press the Enter key. Now you get the second date of a month. See screenshot:

8. Keep selecting cell C6, then drag the Fill Handle to the right cell until it reaches the end of the month. Now the whole monthly calendar is created.

9. Then you can format the date to your need. Select all listed date cells, then click Home > Orientation > Rotate Text Up. See screenshot:

10. Select the whole columns containing all date cells, right click the column header and click Column Width. In the popping up Column Width dialog box, enter number 3 into the box, and then click the OK button. See screenshot:

11. Select all date cells, press Ctrl + 1 keys simultaneously to open the Format Cells dialog box. In this dialog box, click Custom in the Category box, enter ddd dd into the Type box, and then click the OK button.

Now all dates are changed to the specified date format as below screenshot shown.

You can customize the calendar to any style as you need. After changing the month or year in corresponding cell, dates of the monthly calendar will dynamically adjust to the specified month or year.

Tip.If you want to quickly calculate age based on date of birth in Excel withoput remembering formulas, please try the Kutools for Excel's Calculate age based on birthday function. You can easily calculate age based on given date of birth with several clicks in Excel as below screenshot shown. You can go to free download the software with no limitation in 60 days.

arrow blue right bubbleRelated articles:

Excel Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for you.

  • Designed for 1500+ work scenarios, helps you solve 80% Excel problems.
  • Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  • Being used by 110,000 elites and 300+ well-known companies.

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Shivang · 5 months ago
    the dates and days are changing but the data in the coloumns is static, its not changing when we change the month? please help
  • To post as a guest, your comment is unpublished.
    John · 5 months ago
    Is is possible to adjust formulas so they do not create extra days for February and and if month have 30 days?
  • To post as a guest, your comment is unpublished.
    Anish Kumar Soni · 1 years ago
    thanks this is very helpful for me. again thanks