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 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.
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:
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
Related articles
How to create a drop down list calendar (date picker) in Excel?
With drop down calendar in Excel worksheet, you can easily select a specific date from it without manually typing. This tutorial shows you how to create a calendar in Excel worksheet using the ActiveX Control.
How to pop up a calendar when clicking a specific cell in Excel?
This article is talking about popping up a calendar when clicking on cells in a certain range, then inserting date into the selected cell automatically after selecting date in the calendar.
How to create appointment from Excel sheet to Outlook calendar?
In sometimes,you may need to create appointments in Outlook calendar, but have you ever tried to list appointments in Excel worksheet, then import them to Outlook calendar as below screenshot shown?
How to create a dynamic monthly calendar in Excel?
This article will show you method to create a dynamic monthly calendar in Excel in details.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.