How to import and export nonworking time in MS Project

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

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

13 thoughts on “How to import and export nonworking time in MS Project

  1. jan

    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

    Reply
    1. dawed

      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”

      Reply
    2. Noel

      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

      Reply
  2. Sai Prasad

    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?

    Reply
  3. Mihai

    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!

    Reply
    1. Stefan Waldherr Post author

      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.

      Reply
  4. Josef

    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

    Reply
  5. Ofir

    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?

    Reply
    1. Stefan Waldherr Post author

      Hi, Ofir – I’ll change the macros in the upcoming weeks. Check back by mid August. Cheers, Stefan

      Reply
  6. Jorn

    Hi,

    is there already a solution available for the error as described by Josef?

    I have the same error popping up…

    Reply
    1. Jorn

      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!

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *