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