Create Appointments for Every X Workday

A common request is how to make recurring appointments for every x number of workdays. Unfortunately Outlook does not offer this type of recurring option. You can use a third party add-in, WS:Repeat Appointment II, to create recurrences not supported by Outlook. If this is a frequent need, its well worth the cost but if your needs are infrequent, you can use Excel to create the recurrence pattern and import it into Outlook.

You can either start with a blank workbook and create your own fields which will need mapped to Outlook's fields when you import it, or export your calendar from Outlook to Excel, then delete the appointments from the workbook, leaving just the field names. At the very least you should use the start and end times, along with the subject field and the date field of course.

Row 1 contains the field names.

Cell A2 contains the starting date.

Cell A3 contains the formula you need to calculate workdays: =WORKDAY(A2,4,F2:F147) The first parameter is the cell it bases the date calculation on. The second is the number of days. In my example, the calculation is for the 4th workday. The final parameter is an array of holidays. You are only required to to use the first two parameters =WORKDAY(A2,4) if you don't need to consider holidays, only weekdays.

Drag Cell A3 down to fill the cells. If the subject and time fields will be the same for each appointment, drag to fill these cells too. Use the method at Create Countdown Calendar Items to fill the subject field with consecutive numbers, such as Meeting 1, Meeting 2, etc.

I used the "Use Custom View Data in Another Program" method to display a list of the holiday dates in Outlook, which I copied and pasted into Excel. You'll need to change the Field format in Outlook to display just the date for it to work. Do this by right clicking on the field name and choose Format columns.

When you are finished creating the spreadsheet, select and copy the cells then use Paste Special, Values to replace the formulas with the cell values. Delete the list of holidays from the spreadsheet.

To import the appointments into Outlook:
  1. Select the entire used range in Excel and type a name for it in the named range field above column A.
  2. Save and close the workbook.
  3. Switch to Outlook.
  4. Select File, Import and Export
  5. Select Import from another program or file.
  6. Select the program or file type. In this example, we're using Excel to create the file, so we choose Microsoft Excel
  7. Browse for the file. (If the file is still open in Excel, it will cause an error.)
  8. Select the Calendar folder you want to Import the file into.
  9. Click to check the box beside Import [named range] to [folder] and if fields need mapped the Map Custom fields dialog opens.
  10. Outlook doesn't recognize Date, so I need to drag it to Start Date and Outlook adds it to the Mapped From column next to Start Date.  


  11. Click Finish to import your items.

 

 

 

Importing lists from Excel to Outlook (Video tutorial from slipstick.com)