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

Kutools for Excel: add 120 new features in Excel. Save one hour every day.
Classic Menu for Office: brings back classic menus to Office 2010 and 2013 (includes Office 365).
Office Tab: brings tabbed interface to Office as the use of web browser Chrome, Firefox and Internet Explorer.

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

Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

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 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.


Kutools for Excel

More than 120 Advanced Functions for Excel 2013, 2010, 2007 and Office 365.

screen shot

btn read more     btn download     btn purchase

Add comment

Security code