跳到主要內容

如何在Excel中創建日曆?

作者:技術支持 最後修改時間:2024-01-29

Excel 行事曆可協助您追蹤重要事件,例如某人何時開始新工作或何時需要交付某些東西。它使查看這些日期變得簡單明了。在本指南中,我將向您展示如何在 Excel 中製作月曆和年曆。我們將研究如何使用模板進行快速設置,以及如何為想要更多控制的人從頭開始製作模板。這樣,無論是工作計劃還是個人計劃,您都可以保持井井有條。

使用 Excel 日曆範本建立年度日曆

使用Kutools for Excel快速創建每月或每年的日曆

使用VBA代碼創建每月日曆


使用 Excel 日曆範本建立年度日曆

使用此方法,必須確保計算機連接到網絡,以便可以下載 日曆模板.

1。 去 文件 標籤,點擊 全新 按鈕,然後按一下左側窗格中的 日曆 建議的搜尋。 看截圖:

2. 選擇您喜歡的日曆範本之一,雙擊它以建立年曆。

結果


使用Kutools for Excel快速創建每月或每年的日曆

Perpetual Calendar 萬年曆 的工具 Excel 的 kutools 可以在新工作簿中快速創建自定義的月曆或年曆,每個月曆將包含在新工作表中。

注意: 要應用此 Perpetual Calendar 萬年曆 功能,首先,您應該下載並安裝 Excel的Kutools.

安裝Kutools for Excel之後,請點擊 Kutools 加 > 下載學習單 > Perpetual Calendar 萬年曆。 在彈出的 Perpetual Calendar 萬年曆 對話框,請執行以下操作:

  • 若要建立月曆,請透過以下方式指定要建立日曆的月份: 下拉列表,然後單擊 創建.
  • 若要建立年曆,請透過以下方式指定要建立日曆的年份: 下拉列表,然後單擊 創建.
結果
  • 每月日曆:
  • 每年的日曆:
尖端:要使用此功能,您應該安裝 Excel的Kutools 首先,請 點擊下載並獲得 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