跳到主要内容

如何在Excel中创建日历?

作者:技术支持 最后修改时间:2024-01-29

Excel 日历可帮助您跟踪重要事件,例如某人何时开始新工作或何时需要交付某些东西。它使查看这些日期变得简单明了。在本指南中,我将向您展示如何在 Excel 中制作月历和年历。我们将研究如何使用模板进行快速设置,以及如何为那些想要更多控制的人从头开始制作模板。这样,无论是工作计划还是个人计划,您都可以保持井井有条。

使用 Excel 日历模板创建年度日历

使用 Kutools for Excel 快速创建月度或年度日历

使用VBA代码创建每月日历


使用 Excel 日历模板创建年度日历

使用此方法,必须确保计算机连接到网络,以便可以下载 日历模板.

1。 去 文件 标签,点击 全新 按钮,然后单击左侧窗格中的 日历建议的搜索。 看截图:

2. 选择您喜欢的日历模板之一,双击它以创建年历。

结果


使用 Kutools for Excel 快速创建月度或年度日历

Perpetual Calendar 的工具 Excel 的 kutools 可以在新工作簿中快速创建自定义的月历或年历,每个月历将包含在新工作表中。

请注意: 申请这个 Perpetual Calendar 功能,首先,您应该下载并安装 Kutools for Excel.

安装 Kutools for Excel 后,请单击 Kutools 加 > 工作表 > Perpetual Calendar。 在弹出的 Perpetual Calendar 对话框,请执行以下操作:

  • 要创建月历,请通过以下方式指定要创建日历的月份: 下拉列表,然后单击 创建.
  • 要创建年历,请通过以下方式指定要创建日历的年份: 下拉列表,然后单击 创建.
结果
  • 每月日历:
  • 每年的日历:
Tips::要使用此功能,您应该安装 Kutools for Excel 首先,请 点击下载并享受 30 天免费试用 现在。

使用VBA代码创建每月日历

使用以下VBA代码,您可以快速创建每月日历。 请执行以下操作:

1。 按 其他 + F11 键以打开“ Microsoft Visual Basic应用程序”窗口。

2。 将显示一个新窗口。 请点击 插页 > 模块,然后在模块中输入以下代码:

 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。 然后点击 运行 按钮或按下 F5 运行应用程序的键。此时会弹出一个提示框,您可以在空白框中输入月份和年份。

结果

Comments (3)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
Hi, How can I make it start on Monday instead? Thanks in advance
This comment was minimized by the moderator on the site
Excellent job. Billions thanks for your great design of the Excel calendar template. :lol:
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations