Method 1 – Setting Up Monthly Schedule
- Type the year and month.
- Type the time. Here, we have used a one-hour interval to keep it simple. You can increase it as needed.
- Type the following formula in cell C7.
=DATE(B5,C5,1)
- This formula returns the date part of the specified month and year.
- Press ENTER. We will get the first date of the specified date. We used a custom format of ddd dd to get the day of the week and the date format.
- Type another formula in cell D7.
=C7+1
- This formula increases the dates by 1.
- AutoFill the formula on the right side to get all the dates from that month, and this concludes the first step.
Method 2 – Entering Tasks
- Type the tasks into the monthly calendar to create a schedule.
- We typed the tasks as Task 01, Task 02, Task 03, and Task 04. You can type whatever your objective is.
- Or do it using VBA.
- Press ALT+F11 to bring up the VBA Developer window.
- From Insert, select Module. We will type our code here.
- 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
- Declare all the variable types.
Option Explicit- Calling the Sub procedure.
Sub Recurring_Task()- Setting the variable types.
Dim Task_Value As String
Dim x1 As Integer- Setting the task name to repeat.
Task_Value = "Task 01"- Using a For Next loop, we add the value more than once. The G column is 7 and the K column is 11. We keep row 16 constant to keep the time at 5:00 PM. Using the “Cell.Value” method, 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- Run the code, and it will fill the cell range G16:K17 with the specified task.
- You can separate the weekends with a different color.
You can download the Excel file from the link below.
Related Articles
- How to Create a Schedule in Excel That Updates Automatically
- How to Make a Daily Schedule in Excel
- How to Make an Hourly Schedule in Excel
<< Go Back to Excel for Business | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
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.
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.