• Outlook User
  • Exchange Admin
  • Office 365
  • Outlook Developer
  • Outlook.com
  • Outlook Mac
  • Outlook & iCloud
    • Common Problems
    • Outlook BCM
    • Utilities & Addins

Create Appointments for Every X Workday (Using Excel)

Slipstick Systems

› Outlook › Calendar › Create Appointments for Every X Workday (Using Excel)

Last reviewed on February 8, 2018     45 Comments

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.

Use VBA to create appointments for every xx weekday

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. Or download an Excel workbook with the calendar fields and a few lines of sample data.

If you create the workbook yourself, you should use the start and end time fields, along with the subject field and the date field, of course. Any other fields are optional.

Prepare the Excel Workbook

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.

Enter the dates to be skipped in Column F.

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.

Use Excel to create appointments

  1. When you are finished creating the spreadsheet, select and copy the cells you will be importing then use Paste Special, Values to paste the data in Sheet 2. By doing this you will avoid the need to make a named range.
  2. Save Sheet 2 using Comma Separated Value (CSV) format.

If you want to save the workbook with the formulas intact, choose Save As and save it in Excel format.

To import the appointments into Outlook

In Outlook:

  1. Select File, Import and Export (File, Open, Import in Outlook 2010)
  2. Select Import from another program or file.
  3. Select the program or file type. In this example, we're using Comma Separated Value (CSV)
  4. Browse for the file. (If the file is still open in Excel, it will cause an error.)
  5. Select the Calendar folder you want to Import the file into.
  6. If fields need mapped, use the Map Custom fields dialog.
  7. 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.
    use the map custom fields dialog
  8. Click Finish to import your items.

More Information

Importing lists from Excel to Outlook

Create Appointments for Every X Workday (Using Excel) was last modified: February 8th, 2018 by Diane Poremsky
  • Twitter
  • Facebook
  • LinkedIn
  • Reddit
  • Print

Related Posts:

  • Many users have contact data stored in Excel workbooks or created cale
    Importing Lists from Excel to Outlook
  • The No-Export way to use Outlook data in Excel
  • One (very justified) complaint about Outlook recurrence patterns is th
    Create Outlook appointments for every nn workday
  • Create Appointments Using Spreadsheet Data

About Diane Poremsky

A Microsoft Outlook Most Valuable Professional (MVP) since 1999, Diane is the author of several books, including Outlook 2013 Absolute Beginners Book. She also created video training CDs and online training classes for Microsoft Outlook. You can find her helping people online in Outlook Forums as well as in the Microsoft Answers and TechNet forums.

Subscribe
Notify of
45 Comments
newest
oldest most voted
Inline Feedbacks
View all comments

Shannon (@guest_218971)
December 14, 2021 6:55 pm
#218971

Thank you!! Here we are at the end of 2021 and this is still the only solution to this. You have saved me weeks of work having to enter random deadlines in manually.

0
0
Reply
Senthil (@guest_218654)
August 19, 2021 2:31 pm
#218654

Nice; I did not use the calculation but helped me to import reminders. Thanks. 

0
0
Reply
Ian (@guest_208741)
September 27, 2017 4:26 am
#208741

is this the same for meetings?

0
0
Reply
Diane Poremsky(@diane-poremsky)
Author
Reply to  Ian
October 8, 2017 9:15 pm
#208890

You can't import meetings from a spreadsheet - well you could, but only as appointments. You can't add recipients via import.

0
0
Reply
Ian (@guest_208739)
September 27, 2017 1:24 am
#208739

Then? Where can I see the new option?

0
0
Reply
Diane Poremsky(@diane-poremsky)
Author
Reply to  Ian
October 8, 2017 9:18 pm
#208891

Sorry, I'm not sure which option you are asking about.

0
0
Reply
Anand Joshi (@guest_202528)
October 25, 2016 10:01 am
#202528

When I map the column, I don't see option to Finish. The okay button is still grayed out. What am I doing wrong?

0
0
Reply
Diane Poremsky(@diane-poremsky)
Author
Reply to  Anand Joshi
October 25, 2016 4:38 pm
#202539

Did you map all of the fields? If you use field names outlook uses, you won't need to map them (click the button for Default Map) - you can also get this list by exporting the calendar to a CSV then open it in Notepad.

"Subject","Start Date","Start Time","End Date","End Time","All day event","Reminder on/off","Reminder Date","Reminder Time","Meeting Organizer","Required Attendees","Optional Attendees","Meeting Resources","Billing Information","Categories","Description","Location","Mileage","Priority","Private","Sensitivity","Show time as"

0
0
Reply
Kristen (@guest_213793)
Reply to  Anand Joshi
August 19, 2019 6:42 pm
#213793

Close the mapping window then select finish.

0
0
Reply
mark vanderpol (@guest_198160)
April 24, 2016 10:03 pm
#198160

Why cant they just reference the day of the week, if the day falls on a Saturday or Sunday then it should skip to Monday. problem solved

0
0
Reply
Diane Poremsky(@diane-poremsky)
Author
Reply to  mark vanderpol
October 8, 2017 9:13 pm
#208889

You'd need to use a macro to do that - outlook doesn't support it.

0
0
Reply
Michael F (@guest_195189)
December 8, 2015 4:09 pm
#195189

YES OUTLOOK DOES DO THIS... go to recurrence, select MONTH on the left, then in middle select numbered day you want, then select (in drop down window - scroll UP to more options) 'Business Day".
You will be able to do what you want.

0
-9
Reply
Diane Poremsky(@diane-poremsky)
Author
Reply to  Michael F
December 8, 2015 10:21 pm
#195190

The problem with using weekday is that it's a specific day - the 3rd weekday, not every 3rd weekday. It also only does 1st - 4th + the last weekday of a month.

4
0
Reply
docmomma (@guest_193287)
September 14, 2015 9:33 am
#193287

Why doesn't Outlook just have an option for "Fifth" workday? People have been asking for this since at least 2006, judging by my google hits....

1
0
Reply
Diane Poremsky(@diane-poremsky)
Author
Reply to  docmomma
September 23, 2015 12:17 am
#193502

I believe there are issues calculating it - Outlook needs to know what to do when there are only 4 of that day in a month. The last day and 4th day are easy to calculate (and last is sometimes the 5th).

0
0
Reply

Visit Slipstick Forums.
What's New at Slipstick.com

Latest EMO: Vol. 28 Issue 11

Support Services

Do you need help setting up Outlook, moving your email to a new computer, migrating or configuring Office 365, or just need some one-on-one assistance?

Subscribe to Exchange Messaging Outlook






Our Sponsors

CompanionLink
ReliefJet
  • Popular
  • Latest
  • WeekMonthAll
  • Adjusting Outlook's Zoom Setting in Email
  • How to Remove the Primary Account from Outlook
  • Cannot add Recipients in To, CC, BCC fields on MacOS
  • Move an Outlook Personal Folders .pst File
  • Save Sent Items in Shared Mailbox Sent Items folder
  • Create rules that apply to an entire domain
  • Outlook's Left Navigation Bar
  • Use PowerShell to get a list of Distribution Group members
  • View Shared Calendar Category Colors
  • Remove a password from an Outlook *.pst File
  • Cannot add Recipients in To, CC, BCC fields on MacOS
  • Change Appointment Reminder Sounds
  • Messages appear duplicated in message list
  • Reset the New Outlook Profile
  • Delete Old Calendar Events using VBA
  • Use PowerShell or VBA to get Outlook folder creation date
  • Outlook's Left Navigation Bar
  • Contact's Display Bug
  • Use PowerShell to get a list of Distribution Group members
  • Edit Outlook’s Attach File list
Ajax spinner

Newest Code Samples

Delete Old Calendar Events using VBA

Use PowerShell or VBA to get Outlook folder creation date

Rename Outlook Attachments

Format Images in Outlook Email

Set Outlook Online or Offline using VBScript or PowerShell

List snoozed reminders and snooze-times

Search your Contacts using PowerShell

Filter mail when you are not the only recipient

Add Contact Information to a Task

Process Mail that was Auto Forwarded by a Rule

Recent Bugs List

Microsoft keeps a running list of issues affecting recently released updates at Fixes or workarounds for recent issues in Outlook for Windows.

Outlook for Mac Recent issues: Fixes or workarounds for recent issues in Outlook for Mac

Office Update History

Update history for supported Office versions is at Update history for Office

Outlook Suggestions and Feedback

Outlook Feedback covers Outlook as an email client, including Outlook Android, iOS, Mac, and Windows clients, as well as the browser extension (PWA) and Outlook on the web.

Use Outlook.com Feedback for suggestions or feedback about Outlook.com accounts.

Other Microsoft 365 applications and services




Windows 10 Issues

  • iCloud, Outlook 2016, and Windows 10
  • Outlook Links Won’t Open In Windows 10
  • Outlook can’t send mail in Windows 10: error Ox800CCC13
  • Missing Outlook data files after upgrading Windows?

Outlook Top Issues

  • The Windows Store Outlook App
  • The Signature or Stationery and Fonts button doesn’t work
  • Outlook’s New Account Setup Wizard
  • Outlook 2016: No BCM
  • Exchange Account Set-up Missing in Outlook 2016

Repair PST

Convert an OST to PST

Repair damaged PST file

Repair large PST File

Remove password from PST

Merge Two Data Files

Sync & Share Outlook Data

  • Share Calendar & Contacts
  • Synchronize two computers
  • Sync Calendar and Contacts Using Outlook.com
  • Sync Outlook & Android Devices
  • Sync Google Calendar with Outlook
  • Access Folders in Other Users Mailboxes

Contact Tools

Data Entry and Updating

Duplicate Checkers

Phone Number Updates

Contact Management Tools

Diane Poremsky [Outlook MVP]

Make a donation

Calendar Tools

Schedule Management

Calendar Printing Tools

Calendar Reminder Tools

Calendar Dates & Data

Time and Billing Tools

Meeting Productivity Tools

Duplicate Remover Tools

Mail Tools

Sending and Retrieval Tools

Mass Mail Tools

Compose Tools

Duplicate Remover Tools

Mail Tools for Outlook

Online Services

Productivity

Productivity Tools

Automatic Message Processing Tools

Special Function Automatic Processing Tools

Housekeeping and Message Management

Task Tools

Project and Business Management Tools

Choosing the Folder to Save a Sent Message In

Run Rules on messages after reading

Help & Suggestions

Outlook Suggestion Box (UserVoice)

Slipstick Support Services

Home | Outlook User | Exchange Administrator | Office 365 | Outlook.com | Outlook Developer
Outlook for Mac | Common Problems | Utilities & Addins | Tutorials
Outlook & iCloud Issues | Outlook Apps
EMO Archives | About Slipstick | Advertise | Slipstick Forums
Submit New or Updated Outlook and Exchange Server Utilities

Send comments using our Feedback page
Copyright © 2023 Slipstick Systems. All rights reserved.
Slipstick Systems is not affiliated with Microsoft Corporation.

wpDiscuz

Sign up for Exchange Messaging Outlook

Our weekly Outlook & Exchange newsletter (bi-weekly during the summer)






Please note: If you subscribed to Exchange Messaging Outlook before August 2019, please re-subscribe.

Never see this message again.

You are going to send email to

Move Comment