Some events may happen frequently in your life. You can keep track of it easily with the help of Excel. In this article, we will show you two quick steps to create a recurring monthly schedule in Excel.
How to Create a Recurring Monthly Schedule in Excel: Step-by-Step Procedures
We will show you two quick steps to get to the goal of this article. In the first step, we will show you how to set up the monthly calendar. Then, as the final step, we will enter the repeating events into the monthly schedule using both VBA and manual typing. Lastly, here is a snapshot of the ultimate step of this article.
Step 1: Setting Up Monthly Schedule
In this first step, we will input the desired month and year into the Excel file. Then, using the DATE function, we will get the first date of the month. Next, we will add 1 to get the other dates from that month. Moreover, we will use a custom format to change the date display.
- To begin with, type the year and month.
- After that, type the time. Here, we have used a one-hour interval to keep it simple. You can increase it as per your need.
- Next, type the following formula in cell C7.
- This formula returns the date part of the specified month and year.
- Next, press ENTER. We will get the first date of the specified date. Moreover, we have used a custom format of ddd dd to get the day of the week and the date format.
- After that, type another formula in cell D7.
- This formula increases the dates by 1.
- Then, AutoFill the formula on the right side to get all the dates from that month, and this concludes the first step.
Step 2: Entering Tasks
In our second step, we will enter the tasks into the monthly schedule in Excel. Firstly, we will enter it manually. Then, we will use a VBA macro to do a recurring monthly schedule in Excel. We will use a For Next loop to type the same thing in more than one cell. Without further ado, let’s jump into the steps.
- Firstly, type the tasks into the monthly calendar to create a schedule.
- Here, we have typed the tasks as Task 01, Task 02, Task 03, and Task 04. You can type whatever your objective is.
- That is one way to do it. Now, we are going to show you how to do it using VBA.
- Firstly, press ALT+F11 to bring up the VBA Developer window.
- Secondly, from Insert, select Module. We will type our code here.
- Thirdly, type the following code.
Option Explicit Sub Recurring_Task() Dim Task_Value As String Dim x1 As Integer Task_Value = "Task 01" For x1 = 7 To 11 Step 1 'Values will be in 5 To 9 Sep Cells(16, x1).Value = Task_Value Next x1 End Sub
VBA Code Breakdown
- Firstly, it is mandatory to declare all the variable types.
- Secondly, calling the Sub procedure.
- Thirdly, setting the variable types.
Dim Task_Value As String Dim x1 As Integer
- Fourthly, setting the task name to repeat.
Task_Value = "Task 01"
- Lastly, using a For Next loop, we are adding the value more than once. Here, the G column is 7 and the K column is 11. Then, we keep row 16 constant to keep the time at 5:00 PM. Lastly, using the “Cell.Value” method, we write the date into the G16:K16 range.
For x1 = 7 To 11 Step 1 'Values will be in 5 To 9 Sep Cells(16, x1).Value = Task_Value Next x1 End Sub
- Then, run the code, and it will fill the cell range G16:K17 with the specified task.
- Lastly, you can separate the weekends with a different color, and this concludes our guide to creating a recurring monthly schedule in Excel.
Read More: How to Create a Weekly Schedule in Excel
Download Practice Workbook
You can download the Excel file from the link below.
We have shown you two quick steps to create a recurring monthly schedule in Excel. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. We will solve your query as soon as possible.