How to Create a Recurring Monthly Schedule in Excel: 2 Methods

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.

Setting Up Monthly Schedule to Create a Recurring Monthly Schedule in Excel

  • Type the following formula in cell C7.

=DATE(B5,C5,1)

  • This formula returns the date part of the specified month and year.

The DATE Function

  • 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.

Entering Tasks to Create a Recurring Monthly Schedule in Excel

  • 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.

 


Download Practice Workbook

You can download the Excel file from the link below.


Related Articles


<< Go Back to Make Schedule in Excel | Excel for Business | Learn Excel

 

 

Get FREE Advanced Excel Exercises with Solutions!
Rafiul Haq
Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

2 Comments
  1. 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.

    • Reply Avatar photo
      Mohammad Shah Miran Feb 27, 2023 at 3:53 PM

      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.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo