Note: The other languages of the website are Google-translated. Back to English
English English

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


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.

Date picker (easily select date with specific date format from calendar and insert to selected cell):

Here introduce a useful tool – the Insert Date utility of Kutools for Excel, with this utility, you can easily pick up dates with specific format from a date picker and insert in into selected cell with double-clicking. Download and try it now! ( 30-day free trail)


Related articles:


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (17)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
thanks this is very helpful for me. again thanks
This comment was minimized by the moderator on the site
Is is possible to adjust formulas so they do not create extra days for February and and if month have 30 days?
This comment was minimized by the moderator on the site
the dates and days are changing but the data in the coloumns is static, its not changing when we change the month? please help
This comment was minimized by the moderator on the site
I have the same problem!
This comment was minimized by the moderator on the site
Hi,
Can you tell me your Excel version?
This comment was minimized by the moderator on the site
Any answer about this comment? I really need that to my work
This comment was minimized by the moderator on the site
Has anyone found a solution to this
This comment was minimized by the moderator on the site
Has anyone found a solution to this issue? There must be a work around........
This comment was minimized by the moderator on the site
I did it but I didn't get it this solution why so lengthy. Normally I enter the First date then I drag the date down its gives me full moth calendar automatically. I didn't understand why this so complicated.
This comment was minimized by the moderator on the site
Sir, 9/5/2020.Very clearly and wisely you have shown the steps. I must appreciate your efforts to design the project.I also hope to receive from you more ideas and Tips in future too.Thanking you once again.Kanhaiyalal Newaskar.
This comment was minimized by the moderator on the site
Has anyone found a solution to the issue of dates and days are changing but the data in the coloumns/cells is static, its not changing when we change the month.
This comment was minimized by the moderator on the site
I really appreciate your effort Sir. But since I was using the excel format 2010, in the Format Control dialog box there is no Control tab, so is there any way to input range?
This comment was minimized by the moderator on the site
Hi Fatihah,There are 2 families of controls in Excel: Form Controls and ActiveX Controls.Forms controls have a number of tabs on their Format Control dialog, including Control. However, ActiveX Controls do not have the Control tab on their Format Control dialog.
This article used the Combo Box (Form Control).Please check which combo box you are using.
This comment was minimized by the moderator on the site
День добрый.Создал по Вашему примеру календарь в одну строку, но есть одна проблема.При выборе месяцев, где дней меньше чем 31, например Февраль, после последнего дня в феврале в календаре показываются три первых дня марта.01.02.21 02.02.21 03.02.21 04.02.21 05.02.21 06.02.21 07.02.21 08.02.21 09.02.21 10.02.21 11.02.21 12.02.21 13.02.21 14.02.21 15.02.21 16.02.21 17.02.21 18.02.21 19.02.21 20.02.21 21.02.21 22.02.21 23.02.21 24.02.21 25.02.21 26.02.21 27.02.21 28.02.21 01.03.21 02.03.21 03.03.21
Как можно скрыть отображение этих лишних дней?
This comment was minimized by the moderator on the site
Hi does anyone know how to do this without using weekends?
This comment was minimized by the moderator on the site
al escribir = FECHA (A2, A1,1) me sale error
This comment was minimized by the moderator on the site
Hi Manuel,
What kind of erro did you get? If it returns a #NUM! error value, the error value will be replaced with a date after selecting an item from the combo box.
To be mentioned, the formula provided in the post can only be applied in English system environment Excel. If you have Excel in a different language than English, please convert the formula from English to the language you are currently using.
It seems that you are using the Excel in Spanish language system. You need to change the commas in the formula to semicolons.
You can translate the formula in this page: https://de.excel-translator.de/translator/
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations