Not reflecting holidays in project plans can have many nasty side-effects. Tasks end later than you think because people are not available. If you have longer projects, the number of holiday days, bridging days and other nonworking time add up and can significantly extend your project far beyond your established deadline.
Using “Tools | Change working time … ” in MS Project you can change both the project calendar and the calendar of each team member. However, this process is cumbersome. The following macros update nonworking time from an Excel file.
For the macros to work, simply create an Excel file with the nonworking time, like so:

Excel file with nonworking time for each team member and project holidays
Structure of the file
- In column A, rows 6 et. seq. list each team member (same name as in the project file) with the nonworking time. Nonworking time in my projects is defined as every day where the resource cannot work on the project (i.e. is sick, on holiday, not available, working for other projects, etc.). The macro stops on the first empty line.
- In column K, rows 6 et. seq. list each national holiday or project holiday. Be creative and list as many days as you can think of – they also serve as a buffer. The macro stops on the first empty line.
- Ignore columns F thru I.
There are four macros that you can run from your project
swaNonWorkingTimeProjectRead
shows a message box with all nonworking days from project start until end of the following year (e.g. your project started on January 1st, 2013; you run this macro on April 1st, 2013 and it will display all nonworking days until end of 2014).swaNonWorkingTimeProjectRemoveAndSet
removes all nonworking days from today until end of the following year. In a second step, opens a defined Excel file, reads and sets nonworking days from today. Does not touch the past.swaNonWorkingTimeResoucesRead
shows a message box with all nonworking days from project start until end of the following year for each resource in the project plan. Note that the list for an employee also includes project holidays even if they are not explicitly set for the employee.swaNonWorkingTimeResoucesRemoveAndSet
removes all nonworking days from today until end of the project for each resource in the project plan. In a second step, opens a defined Excel file, reads and sets nonworking days from today forward. Does not touch the past.
Additionally, there are two internal macros
swaNonWorkingTimeProjectRemove
removes all nonworking time from today until end of the following year.swaNonWorkingTimeResoucesRemove
removes all nonworking time from today until end of the following year for each resource in the project plan.
Here is the code for the six macros. You need to change the highlighted lines to suit your needs.
Option Explicit ' ' removes nonworking times from today until end of next year ' Private Sub swaNonWorkingTimeResoucesRemove() Dim fromDate As Date, toDate As Date Dim r As Resource fromDate = Date toDate = CDate("31.12." + CStr(Year(Date) + 1)) For Each r In ActiveProject.Resources If Not (r Is Nothing) Then ' skip resource with zero remaining work If r.RemainingWork > 0 Then With r ' the follwing line removes only the nonworking time of the resource (and sets it to the default calendar entries) ResourceCalendarEditDays ActiveProject.Name, .Name, fromDate, toDate, Working:=True, Default:=True End With Else Debug.Print r.Name & " has 0 remaining work." End If End If Next r End Sub ' ' prints out the unification of personal and global nonworking times ' Sub swaNonWorkingTimeResoucesRead() Dim fromDate As Date, toDate As Date, day As Date Dim r As Resource Dim Nonworkingtime As String fromDate = ActiveProject.ProjectStart toDate = CDate("31.12." + CStr(Year(Date) + 1)) Nonworkingtime = "" For Each r In ActiveProject.Resources Nonworkingtime = "" If Not (r Is Nothing) Then ' skip resource with null remaining work If r.RemainingWork > 0 Then For day = fromDate To toDate With r.Calendar If .Period(day).Working = False Then If Not .WeekDays(Weekday(day)).Working Then 'Debug.Print R.Name & " " & "Weekly Holiday", day ' that day came from the overall global calendar, typically saturday/sunday Else ' Debug.Print "Exception Holiday", day ' that day was manually set (either in global holiday or in resource calendar Nonworkingtime = Nonworkingtime & Format(day, "dd.mm.yyyy") & vbNewLine 'Debug.Print R.Name & " " & "Exception Holiday", day ' that day was manually set (either in global holiday or in resource calendar End If End If End With Next MsgBox Nonworkingtime, vbInformation, r.Name End If End If Next r End Sub ' ' open excel file, for reach resource remove all nonworking time until project end and update future nonworking times ' Sub swaNonWorkingTimeResoucesRemoveAndSet() Dim ExcelApp As Object, swaWorkbook As Object Dim Arr() As Variant Dim swaWorksheet As Worksheet Dim r As Resource Dim i, runto As Integer Dim Ri As Long Dim swaRange As String Dim StartTime As Double Dim xlCalc As XlCalculation Set ExcelApp = CreateObject("Excel.Application") ExcelApp.Visible = False Set swaWorkbook = ExcelApp.Workbooks.Open("C:\GM_Reporting-edta-new\GM_Reporting_Absence.xls") Set swaWorksheet = ExcelApp.Worksheets(1) ' work with first tab ' read nonworking times runto = swaWorksheet.Range("A65536").End(xlUp).Row swaRange = "A6:C" & runto Arr = swaWorksheet.Range(swaRange) ' suck in until last nonempty row ' add sanity check for all dates and throw error i = 0 For Ri = 1 To UBound(Arr, 1) If i = 0 Then If Not (IsDate(Arr(Ri, 2))) Then i = Ri If Not (IsDate(Arr(Ri, 3))) Then i = Ri End If Next Ri If i = 0 Then Else MsgBox "Wrong date (at least) in nonworking data from " & Arr(i, 1), vbCritical, "Error" swaWorkbook.Close (True) ExcelApp.Quit End End If Application.DisplayStatusBar = True ' turn off calculation or this will take looooog xlCalc = Application.Calculation Application.Calculation = xlCalculationManual On Error GoTo CalcBack StartTime = Timer For Each r In ActiveProject.Resources If Not (r Is Nothing) Then ' skip resource with zero remaining work If r.RemainingWork > 0 Then With r ' the follwing line removes only the nonworking time of the resource (and sets it to the default calendar entries) ResourceCalendarEditDays ActiveProject.Name, .Name, Date, ActiveProject.ProjectFinish, Working:=True, Default:=True Application.StatusBar = "Updating nonworking time of " & .Name ' loop throu Arr, if name found, add entry to non-working time of resource For Ri = 1 To UBound(Arr, 1) ' First array dimension is rows. If Arr(Ri, 1) = .Name Then ' update nonworking time iff Finish >= Current date (we do not update nonworking time of the past) If CDate(Arr(Ri, 3)) >= Date Then ResourceCalendarEditDays ActiveProject.Name, .Name, Arr(Ri, 2), Arr(Ri, 3), Working:=False, Default:=False Else Debug.Print "Not adding " & .Name & " " & Arr(Ri, 2) & " to " & Arr(Ri, 3) End If End If Next Ri End With Else Debug.Print r.Name & " has 0 remaining work." End If End If Next r Debug.Print Format(Timer - StartTime, "00.00") & " seconds" CalcBack: Application.Calculation = xlCalc Application.StatusBar = "Done." ' close Worksheet swaWorkbook.Close (True) ExcelApp.Quit End Sub ' ' prints out project nonworking times from today until end of following year ' Sub swaNonWorkingTimeProjectRead() Dim fromDate As Date, toDate As Date, day As Date Dim Nonworkingtime As String fromDate = ActiveProject.ProjectStart toDate = CDate("31.12." + CStr(Year(Date) + 1)) Nonworkingtime = "" For day = fromDate To toDate With ActiveProject.BaseCalendars("Standard") If .Period(day).Working = False Then If Not .WeekDays(Weekday(day)).Working Then 'Debug.Print "Weekly Holiday", day ' that day came from the overall global calendar, typically saturday/sunday Else Debug.Print "Exception Holiday", day ' that day was manually set (either in global holiday or in resource calendar Nonworkingtime = Nonworkingtime & Format(day, "dd.mm.yyyy") & vbNewLine End If End If End With Next day MsgBox Nonworkingtime, vbInformation, "Base Calendar contains ..." End Sub ' ' Removes nonworking times from today until end of following year ' Private Sub swaNonWorkingTimeProjectRemove() Dim fromDate As Date, toDate As Date, day As Date fromDate = Date toDate = CDate("31.12." + CStr(Year(Date) + 1)) Application.BaseCalendarEditDays Name:="Standard", StartDate:=fromDate, EndDate:=toDate, Default:=True End Sub ' ' opens excel file, removes project nonworking times from today until end of following year and updates future, project (not resource) nonworking times from file ' Sub swaNonWorkingTimeProjectRemoveAndSet() Dim ExcelApp As Object, swaWorkbook As Object Dim Arr() As Variant ' declare an unallocated array. Dim swaWorksheet As Worksheet Dim r As Resource Dim i, runto As Integer Dim Ri As Long Dim swaRange As String Dim xlCalc As XlCalculation Set ExcelApp = CreateObject("Excel.Application") ExcelApp.Visible = False Set swaWorkbook = ExcelApp.Workbooks.Open("C:\GM_Reporting-edta-new\GM_Reporting_Absence.xls") Set swaWorksheet = ExcelApp.Worksheets(1) ' work with first tab ' last element of column K runto = swaWorksheet.Range("K65536").End(xlUp).Row swaRange = "K6:L" & runto Arr = swaWorksheet.Range(swaRange) ' add sanity check for all dates and throw error i = 0 For Ri = 1 To UBound(Arr, 1) If i = 0 Then If Not (IsDate(Arr(Ri, 2))) Then i = Ri End If Next Ri If i = 0 Then Else MsgBox "Wrong date (at least) in nonworking data from " & Arr(i, 1), vbCritical, "Error" swaWorkbook.Close (True) ExcelApp.Quit End End If ' remove nonworking times swaNonWorkingTimeProjectRemove Application.DisplayStatusBar = True ' turn off calculation or this will take looooog xlCalc = Application.Calculation Application.Calculation = xlCalculationManual On Error GoTo CalcBack ' set all global holidays from today until project end For Ri = 1 To UBound(Arr, 1) ' First array dimension is rows. If CDate(Arr(Ri, 2)) >= Date Then 'Debug.Print "setting " & Arr(Ri, 1) & " to " & Arr(Ri, 2) Application.StatusBar = "Set global nonworking time of " & Arr(Ri, 2) BaseCalendarEditDays Name:="Standard", StartDate:=Arr(Ri, 2), EndDate:=Arr(Ri, 2), Working:=False, Default:=True End If Next Ri CalcBack: Application.Calculation = xlCalc Application.StatusBar = "Done." ' close Worksheet swaWorkbook.Close (True) ExcelApp.Quit End Sub
I new to VBA programming between MSP and Office,So some of the items might be easy to fix. using system with Office and project version 2010.
I did try to run Macro “swaNonWorkingTimeProjectRemoveAndSet” Unfortunatly the line
1. “Dim swaWorksheet As worksheet” – gave me an error message wrong declaration or simular
2. “Dim xlCalc As XlCalculation” same error as point 1
3. “runto = swaWorksheet.Range(“K65536″).End(xlUp).Row” the XlUp invokes an erroralso.
Hope some body can help me
Hi Jan, can you please post the exact error message?
I had the same pb than you. You have to turn on Excel library :
In the VBA window, Tools -> “References” (Références in french), check “Microsoft Excel 12.0 Library”
Thank you for your fantastic code.
swaNonWorkingTimeResoucesRemoveAndSet() is working fine and is the most important one by far for me
However
swaNonWorkingTimeProjectRead produces a “TYPE MISMATCH” error
swaNonWorkingTimeProjectRemoveAndSet produces a ” “Wrong date (at least) in nonworking data from
swaNonWorkingTimeResoucesRead produces a “TYPE MISMATCH” error
Perhaps you could point me in the right direction
the input is as follows
Main LIst New List – the updated list Holiday List
Name: from: to: Name: from: to: Description Date
r1 16-Dec 16-Dec test 1 13-Dec
Good work. Function swaNonWorkingTimeProjectRemoveAndSet doesn’t use the description of the holiday and all the exceptions have been set to [Unnamed] after running this function. I found there is another way to set name using the function BaseCalendars(calendarName).Exceptions.Add … , but the Add function sets only holidays but doesn’t allow to set Exception working days. Is there way to achieve both – set name, working/non working exception day, pattern, range, etc?
I have tried your macro using Office 2007 (Excel and MS Project) and I get an error saying that the type Resource is not recognized (“Dim r As Resource”)
Can you help? Thanks!
A truly nice website.
OK, I’ll bite. What are columns F:I, “New List – the updated list” for?
Hi Rob, I use another tool to send/receive the timesheets. That tool uses columns F thru I. You can simply ignore them. The only thing you need is a file where columns A thru C and K thru L are filled.
Hi Stefan,
at first thank you for posting some macros for automatic import of non working days in project.
but i have an issue with 2 of your macros.
– swaNonWorkingTimeResoucesRemoveAndSet
– swa
running both macros will last in a Compile error: Message: User defined type not defined
Environment
Project 2013, Excel 2013
Compiler stopps at following line “Dim swaWorksheet As Worksheet” within declaration
Sub swaNonWorkingTimeResoucesRemoveAndSet()
Dim ExcelApp As Object, swaWorkbook As Object
Dim Arr() As Variant
Dim swaWorksheet As Worksheet <–
as I'm an absolutly newby in VBA-programming, I have no idea, how i can solve this issue.
Can you help me?
thnk you in advance
BR Josef
Hi
i liked swaNonWorkingTimeProjectRead
i will be using it.
But the only problem is that when I have a lot of exceptions the Msgbox becomes too long and not all dates are seen, including the OK button.
is there a way to add a scroll bar to the msgbox by code?
Hi, Ofir – I’ll change the macros in the upcoming weeks. Check back by mid August. Cheers, Stefan
Hi,
is there already a solution available for the error as described by Josef?
I have the same error popping up…
Hi all,
The problems got solved
Just sat together with a VBA specialist… The step into function is something that helps us out tremendously.
One thing you should certainly check is that the Excel Library is loaded as mentionned by Dawed.
Then you should make sure that the date format and working time format is changed over the complete macro.
The mistake I made was that I started with a blank project. You should start with a complete project and then load the non-working days…
I hope this helps out others to benefit from this great macro!