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.
Download Practice Workbook
You can download the Excel file from the link below.
Step-by-Step Procedures to Create a Recurring Monthly Schedule in Excel
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 manually 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.
Read More: How to Make an Hourly Schedule in Excel (with Easy Steps)
- How to Create a Monthly Schedule in Excel (3 Simple Ways)
- Create a Project Schedule in Excel (with Easy Steps)
- How to Make a Work Schedule in Excel (3 Handy Examples)
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, making it 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 create a recurring monthly schedule in Excel.
Read More: How to Make a Daily Schedule in Excel (6 Practical Examples)
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. However, remember that our website implements comment moderation. Therefore, your comment may not be instantly visible. So, have a little bit of patience, and we will solve your query as soon as possible. Moreover, you can visit our site, ExcelDemy for more Excel-related articles. Thanks for reading, keep excelling!
Hello. I am working on an excel to help me plan the maintenance of 10 machines.
My maintenance policy is:MNT1 to MNT4 every 15 days, and the pattern repeats its until the end of the year.
Can you help me understand how to develop such a coding please?
Thank you for getting in touch with us. Based on your comment, what i understand is that you are interested in creating a schedule to track your maintenance work. I have provided an Excel file (Machine Maintenance Schedule) that outlines the different types of work you will need to perform. However, I would like to confirm whether your MNT1 and MNT2 etc. tasks are repetitive, or if they only need to be completed once every 15 days.
If you would like to automate this process, you can incorporate a nested for loop to generate the desired output. Additionally, you can develop a sub-function to prevent repetitive values from being generated.
Further, if you have any questions or concerns about this matter, leave your query here. We are here to help and are at your disposal.