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


How to create a calendar in Excel?

The calendar in Excel is a useful object. With a calendar in Excel, you can record many messages in the calendar. Such as: the date that a new employee joining the company, the date that the goods delivered and so on. With the calendar, these messages will look visual and simple. How to create a monthly calendar or a yearly calendar in Excel?

Create a yearly calendar by downloading Excel Calendar Templates

Create a monthly calendar with VBA code

Quickly create a monthly or yearly calendar with Kutools for Excel

arrow blue right bubbleCreate a yearly calendar by downloading Excel Calendar Templates

With this method, you must assure that your computer connect to the network, so that you can download the Calendar Templates.

Go to File, click New on the left pane, and click Calendar from Office.com Templates. See screenshot:


Then you can choose one calendar folder, and click on it, there will be several styles of the calendar, select one you like, and click Download button on the right pane.

After downloading the calendar template, you will get the calendar in a new workbook.

arrow blue right bubble Create a monthly calendar with VBA code

With the following VBA code, you can quickly create a monthly calendar. Please do as follows:

Click Developer > Visual Basic, a new Microsoft Visual Basic for applications window will be displayed, click Insert > Module, and input the following code into the Module:

VBA: create a monthly calendar in Excel

Sub CalendarMaker()
ActiveSheet.Protect DrawingObjects:=False, Contents:=False, _
Application.ScreenUpdating = False
On Error GoTo MyErrorTrap
MyInput = InputBox("Type in Month and year for Calendar ")
If MyInput = "" Then Exit Sub
StartDay = DateValue(MyInput)
If Day(StartDay) <> 1 Then
StartDay = DateValue(Month(StartDay) & "/1/" & _
End If
Range("a1").NumberFormat = "mmmm yyyy"
With Range("a1:g1")
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlCenter
.Font.Size = 18
.Font.Bold = True
.RowHeight = 35
End With
With Range("a2:g2")
.ColumnWidth = 11
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = xlHorizontal
.Font.Size = 12
.Font.Bold = True
.RowHeight = 20
End With
Range("a2") = "Sunday"
Range("b2") = "Monday"
Range("c2") = "Tuesday"
Range("d2") = "Wednesday"
Range("e2") = "Thursday"
Range("f2") = "Friday"
Range("g2") = "Saturday"
With Range("a3:g8")
.HorizontalAlignment = xlRight
.VerticalAlignment = xlTop
.Font.Size = 18
.Font.Bold = True
.RowHeight = 21
End With
Range("a1").Value = Application.Text(MyInput, "mmmm yyyy")
DayofWeek = Weekday(StartDay)
CurYear = Year(StartDay)
CurMonth = Month(StartDay)
FinalDay = DateSerial(CurYear, CurMonth + 1, 1)
Select Case DayofWeek
Case 1
Range("a3").Value = 1
Case 2
Range("b3").Value = 1
Case 3
Range("c3").Value = 1
Case 4
Range("d3").Value = 1
Case 5
Range("e3").Value = 1
Case 6
Range("f3").Value = 1
Case 7
Range("g3").Value = 1
End Select
For Each cell In Range("a3:g8")
RowCell = cell.Row
ColCell = cell.Column
If cell.Column = 1 And cell.Row = 3 Then
ElseIf cell.Column <> 1 Then
If cell.Offset(0, -1).Value >= 1 Then
cell.Value = cell.Offset(0, -1).Value + 1
If cell.Value > (FinalDay - StartDay) Then
cell.Value = ""
Exit For
End If
End If
ElseIf cell.Row > 3 And cell.Column = 1 Then
cell.Value = cell.Offset(-1, 6).Value + 1
If cell.Value > (FinalDay - StartDay) Then
cell.Value = ""
Exit For
End If
End If
For x = 0 To 5
Range("A4").Offset(x * 2, 0).EntireRow.Insert
With Range("A4:G4").Offset(x * 2, 0)
.RowHeight = 65
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.WrapText = True
.Font.Size = 10
.Font.Bold = False
.Locked = False
End With
With Range("A3").Offset(x * 2, 0).Resize(2, _
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Range("A3").Offset(x * 2, 0).Resize(2, _
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
Range("A3").Offset(x * 2, 0).Resize(2, 7).BorderAround _
Weight:=xlThick, ColorIndex:=xlAutomatic
If Range("A13").Value = "" Then Range("A13").Offset(0, 0) _
.Resize(2, 8).EntireRow.Delete
ActiveWindow.DisplayGridlines = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
ActiveWindow.WindowState = xlMaximized
ActiveWindow.ScrollRow = 1
Application.ScreenUpdating = True
Exit Sub
MsgBox "You may not have entered your Month and Year correctly." _
& Chr(13) & "Spell the Month correctly" _
& " (or use 3 letter abbreviation)" _
& Chr(13) & "and 4 digits for the Year"
MyInput = InputBox("Type in Month and year for Calendar")
If MyInput = "" Then Exit Sub
End Sub

Then click doc-callendar-2 button to run the code, and a prompt box will pop out, you can input the month and the year in the blank box. See screenshot:


And then click OK. And a new monthly calendar has been created in the current worksheet. See screenshot:


The VBA code is difficult for most of us, and with this VBA code, you can only create a monthly calendar, if you want to create a yearly calendar quickly and easily, is there any other simple method to do this?

arrow blue right bubble Quickly create a monthly or yearly calendar with Kutools for Excel

Perpetual Calendar tool of kutools for Excel can quickly create a customized month calendar or year calendar in a new workbook, and each month calendar will be contained in a new worksheet.

Kutools for Excel: with more than 120 handy Excel add-ins, free to try with no limitation in 30 days. Get it Now

If you have installed Kutools for Excel, please click Enterprise > Worksheet Tools > Perpetual Calendar, and a Perpetual Calendar dialog box will display. If you want to create a current monthly calendar, just enter 1 in the Number of months box and click Create button. See screenshot:


And then a current monthly calendar has been generated in a new workbook.


And if you want to create a yearly calendar of 2012, please enter number 12 in the Number of months box, and specified the first month to January, 2012 in the left pane. See screenshot:


Then click Create button, it will create the 2012 year calendar in a new workbook.


Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 200 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

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.
    Pat Flanders · 3 years ago
    Hi all,

    I played around with your monthly calendar template and automated it so that it will automatically populate from a simple list of activities.

    You can click forward and back buttons and it flips between months. Also, tries to identify "conflicts" that occur on the same day.

    You may find it helpful.

    Link: http://www.magicratproductions.com/temp/GetFileTemplate.php

    • To post as a guest, your comment is unpublished.
      AD Arnold · 3 years ago
      I am interested in the calendar template with the option to list activities. The link post downloads an file that I am unable to open. If you could send me the file that would be very helpful? Thanks!

    • To post as a guest, your comment is unpublished.
      Sophia · 3 years ago
      This is awesome! Im trying to build something similar to keep track of meetings and deadlines in my office. Do you think its possible to modify it further, so that you can show more than one color coded event in one day? I'd like to show up to 8-10 meetings or events in one day, but will need them to be different colors...
      • To post as a guest, your comment is unpublished.
        Pat Flanders · 3 years ago

        The method used here uses "fill" color ... and I think you can only have one fill color per cell ... so, can't do it that way.

        BUT ... you can change the "font" color. So, you could have multiple events in one cell with different color text.

        Just have to fiddle with it. If you look at my code, you'll see that when there are conflicts, it uses RED font to highlight the word "CONFLICT?" ...

        Take a look at that and you can prolly get something that will work for ya.

  • To post as a guest, your comment is unpublished.
    MrsKaur · 3 years ago
    Thanks guys for helping.

    Would like to be able to have a yearly Calendar in which I can enter items. If you can help that would be great.
  • To post as a guest, your comment is unpublished.
    Maria · 3 years ago

    How can I make it start on Monday instead?
    Thanks in advance
  • To post as a guest, your comment is unpublished.
    Annie Wong · 4 years ago
    Excellent job. Billions thanks for your great design of the Excel calendar template. :lol: