Skip to main content

How to create a calendar in Excel?

An Excel calendar helps you keep track of important events, like when someone starts a new job or when something needs to be delivered. It makes seeing these dates easy and clear. In this guide, I'll show you how to make both monthly and yearly calendars in Excel. We'll look at using templates for a quick setup and also how to make one from scratch for those who want more control. This way, you can stay organized, whether it's for work or personal plans.

Create a yearly calendar by using Excel Calendar Templates

Quickly create a monthly or yearly calendar with Kutools for Excel

Create a monthly calendar with VBA code


Create a yearly calendar by using Excel Calendar Templates

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

1. Go to File tab, click New button on the left pane, and click Calendars from Suggested searches. See screenshot:

2. select one of the calendar templates that you like, double-click on it to creat the yearly calendar.

Result


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.

Note: To apply this Perpetual Calendar feature, firstly, you should download and install Kutools for Excel.

After installing Kutools for Excel, please click Kutools Plus > Worksheet > Perpetual Calendar. In the popping-up Perpetual Calendar dialog box, please do as follows:

  • To create a monthly calendar, specify the months you want to create the calendar through the From andTo drop-down list, and click Create.
  • To create a yearly calendar, specify the year you want to create the calendar through the From and To drop-down list, and click Create.
Result
  • A monthly calendar:
  • A yearly calendar:
Tip: To use this feature, you should install Kutools for Excel first, please click to download and have a 30-day free trial now.

Create a monthly calendar with VBA code

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

1. Press Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

2. There will be a new window displayed. Click Insert > Module, then input the following codes in the module:

 Sub CalendarMaker()
ActiveSheet.Protect DrawingObjects:=False, Contents:=False, _
Scenarios:=False
Application.ScreenUpdating = False
On Error GoTo MyErrorTrap
Range("a1:g14").Clear
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/" & _
Year(StartDay))
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
Next
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, _
7).Borders(xlLeft)
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Range("A3").Offset(x * 2, 0).Resize(2, _
7).Borders(xlRight)
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
Range("A3").Offset(x * 2, 0).Resize(2, 7).BorderAround _
Weight:=xlThick, ColorIndex:=xlAutomatic
Next
If Range("A13").Value = "" Then Range("A13").Offset(0, 0) _
.Resize(2, 8).EntireRow.Delete
ActiveWindow.DisplayGridlines = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True
ActiveWindow.WindowState = xlMaximized
ActiveWindow.ScrollRow = 1
Application.ScreenUpdating = True
Exit Sub
MyErrorTrap:
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
Resume
End Sub 

3. Then click Run button or press F5 key to run application. Now a prompt box will pop out, you can input the month and the year in the blank box.

Result