Author: Jim Peter (Baltimore)

Jim Peter is a retired engineering planner with over three decades of experience in project planning and scheduling. He has a BS in Electrical Engineering, and MS in Computer Science, and an MS in Technical Management. He has written numerous custom VBA macros in MS Project. He is a native and resident of Baltimore. See the availability of MS Project Holiday Calendars here.

Add Juneteenth to Your MS Project Holiday Calendar

In 2021, Juneteenth was recognized as a US federal holiday to commemorate the emancipation of enslaved African-Americans and honor Black culture. It has been celebrated annually on June 19 in various parts of the United States since 1865. If you are already using a holiday calendar in MS Project, below are the dates to add in for Juneteenth. Beware that the default holiday calendar in Project is empty; it does not include any legal holidays. You are; therefore, forced to construct or import one on your own. Without the use of a holiday calendar, projects can fall behind by up to two weeks per year. As I’ve shared previously, a complete, pre-populated holiday calendar through 2050 is available at MSProjectHolidayCalendars.com. It includes every national holiday in the US or Canada, including Juneteenth, and, I hope, it will save you from the time-consuming and error-prone process of researching and manually inserting the holidays into y our own calendar.   Holiday Name Start & Finish   Holiday Name Start & Finish 2021 Juneteenth (Jun 19) ->Fri 06/18/21 2036 Juneteenth (Jun 19) 06/19/36 2022 Juneteenth (Jun 19) ->Mon 06/20/22 2037 Juneteenth (Jun 19) 06/19/37 2023 Juneteenth (Jun 19) 06/19/23 2038 Juneteenth (Jun 19) ->Fri 06/18/38 2024 Juneteenth (Jun 19) 06/19/24 2039 Juneteenth (Jun 19) ->Mon 06/20/39 2025 Juneteenth (Jun 19) 06/19/25 2040 Juneteenth (Jun 19) 06/19/40 2026 Juneteenth (Jun 19) 06/19/26 2041 Juneteenth (Jun 19) 06/19/41 2027 Juneteenth (Jun 19) ->Fri 06/18/27 2042 Juneteenth (Jun 19) 06/19/42 2028 Juneteenth (Jun 19) 06/19/28 2043 Juneteenth (Jun 19) 06/19/43 2029 Juneteenth (Jun 19) 06/19/29 2044 Juneteenth (Jun 19) ->Mon 06/20/44 2030 Juneteenth (Jun 19) 06/19/30 2045 Juneteenth (Jun 19) 06/19/45 2031 Juneteenth (Jun 19) 06/19/31 2046 Juneteenth (Jun 19) 06/19/46 2032 Juneteenth (Jun 19) ->Fri 06/18/32 2047 Juneteenth (Jun 19) 06/19/47 2033 Juneteenth (Jun 19) ->Mon 06/20/33 2048 Juneteenth (Jun 19) 06/19/48 2034 Juneteenth (Jun 19) 06/19/34 2049 Juneteenth (Jun 19) ->Fri 06/18/49 2035 Juneteenth (Jun 19) 06/19/35 2050 Juneteenth (Jun 19) ->Mon 06/20/50 Table 1- Users who have an existing holiday calendar should add Juneteenth to avoid scheduling issues.

How Visual Basic for MS Project Changed my Life

Doing the Impossible In 2010, I attended a Baltimore MPUG meeting that changed my life. I learned about Visual Basic for Applications (VBA) for Microsoft Project, and I was stunned to discover easily-performed operations that I had previously thought were impossible. What is VBA? Visual Basic for Applications, or VBA, is behind the scenes in all of the Microsoft Office applications. When you expose the “Developer” tab you will see it (shown in Figure 1 below).     VBA is an object-oriented language, which means that an “object” has “properties” and “methods.” An example object is “Task,” and for that an example property is “Name” and an example method is “Delete.” A very respectable way to discover the VBA instruction for any action is to record a macro. Here is the macro recording of adding one holiday to a holiday calendar (Figure 2).     The space-underscore at the end of one line allows the instruction to continue on the next line. To learn about available objects and their associated properties and methods, see the Object Model via the Help tab (Figure 3).     Here’s an example of doing the impossible! Suppose that you need to create a text file containing a task’s Notes and Successors (Figure 4). You could open the Gantt Chart, copy each field, and paste the data into the text file, but you’ll be limited because only the first 255 characters can be copied (Figure 5). In contrast, the VBA instructions in Figure 6 will produce the full strings as shown in Figure 7.         As you can see using VBA has its advantages.  Here are some personal examples from the last decade: Searching for redundant logic in MS Project, and then deleting each redundancy. Exporting monthly resource-loading into an Excel spreadsheet in the exact format required to upload that into an enterprise Resource Planning System. Building a summary schedule for populating a spacecraft circuit board, based upon a detailed Excel work-flow report from an enterprise Manufacturing Planning System. Finding the months spanned by tasks in each WBS element. Tracing the driving predecessors path to any selected task. Tracing the driven successors path from any selected task. I recently developed US and Canadian holiday calendars for the years 2020-2050, which yield much more realistic schedules than assuming every weekday is a workday. I’ll demonstrate below how I used VBA to automate this production. Automating the Process to Generate a Holiday Calendar Using VBA, I determined the actual dates for all of the fixed-day holidays, such as the Canadian “Family Day” on the third Monday in February. Here is the VBA code to find the third Monday in February (Figure 8). The results were written into comma-separated-values file for this one and all the other fixed-day holidays.   The fixed-date holidays (for example, the US’ Independence Day on July 4) were handled manually in an Excel spreadsheet, making adjustments for those instances where the dates fell over a weekend. Once I had determined all the holidays, I populated the list within MS Project. Doing this manually is error-prone and time-consuming, so I used the VBA instruction discovered in Figure 2 along with Excel formulas to construct a sequence of 350 VBA instructions. See Figure 9.     At this point, I simply copied the VBA instructions from the Excel spreadsheet, pasted them into an empty VBA module, and executed them. You can see the results here.   Going Forward with Your Own Use of VBA Getting started is hard. In the below resources list, I have posted a VBA structural template with all of the necessary sections, along with some hard-to-discover instructions and a few consultants I recommend. I’ve also included a collection of VBA code snippets for the most-likely requirements.   Resources #1. VBA template and code snippets available via my Google Drive. #2. Book:  VBA for MS Project by Rod Gill. Out of print, but a PDF is available via MSProjectHolidayCalendars.com. A few used physical books remain at Amazon.com. #3. Consultant:  Rod Gill via rodg@project-systems.co.nz and at Microsoft Project VBA development services (project-systems.co.nz) and also at Introduction to Project VBA – how to boost your productivity – YouTube #4. Consultant:  Minerva Goree | Microsoft Project Consultant and Teacher | Udemy | info.madschedules@gmail.com #5. Consulting Company:  Sarah Howard via sarah.howard@projility.com #6 Microsoft official reference at Project Visual Basic for Applications (VBA) reference | Microsoft Docs What have you done in MS Project with VBA? I’d love to hear from you in the comments.

Microsoft Project Holiday Calendars 2020-2050 Available for Download

Are you already using the Holiday Calendar feature in MS Project? If you aren’t or you want to learn more about how to make your schedule more accurate, keep reading! The default “holiday calendar” in MS Project is called “Standard” (see Fig 1), and actually has no holidays in it (see Fig 2). If it did, it would look like Fig 3.       The official list of US Federal Holidays is provided here: Federal Holidays (opm.gov). There are 11, including the recent addition of Juneteenth. That’s more than two weeks’ worth of non-work days! If you aren’t using a holiday calendar in MS Project, your schedule could be off by two weeks at any given time. In addition to holidays, most staff take a day of vacation on the Friday after Thanksgiving, as well as take time off during the period between Christmas Day and New Year’s Day. Given all this, the holiday calendar is error-prone and a pain to construct. Some holidays have a fixed-date which requires re-jiggering for the preceding Friday or succeeding Monday when such a holiday falls on a weekend. Other holidays have fixed days such as “the fourth Thursday in November.” This requires determining the actual date each year. There are 340 such calendar entries in the years spanning 2020-2050. I’m a passionate believer in the holiday calendar. Newly-retired and motivated by the recent addition of Juneteenth, I attacked the construction and I’ve now created a “strict” calendar version that includes only the official federal holidays, as well as a “realistic” calendar version that also includes the Friday after Thanksgiving and the entire period from Christmas Eve through New Year’s Day. I recommend using the latter—no one will criticize you for avoiding scheduling events during Christmas break. Note that a holiday calendar does not prevent staff from working—it just avoids scheduling on certain dates. You can obtain a template file containing both calendar versions from the website MSProjectHolidayCalendars.com. There is a $10 fee.  These calendars were auto-generated using custom code plus some manual editing, so they are quite reliable.  I’d love to hear from you in the comments below if you try it.