none
Project 2013 - How to create a resource working time 'exception' report

    Question

  • Hi all, I'm hoping someone can assist me (I'm a beginner with project).  

    Within Project Pro 2013 I've created an 8 month project plan and assigned my resources.  I've then used the 'resource sheet' and double clicked on each of the resources to set their working time unavailability (due to leave, training etc.) by clicking on the 'Change Working Time' button under the 'Resource Information' dialogue box (which appears when you double click the resource under the resource sheet).

    What I'd like to do now is create a report which lists all of my resources and shows their 'unavailability' (i.e. working time exceptions) in a table (or calendar) so I can assess which of my resources have overlapping/conflicting unavailable times (i.e. leave etc.).

    All assistance appreciated.

    cheers Tony

    Wednesday, February 13, 2013 7:55 AM

Answers

  • Hi Tony,

    Team planner view shows the "Non Working time" for resources. Select View >> Team Planner

    Normally it shows non working time in "red". You can customize it by right clicking in Time scale >> Non Working time.

    Thanks,

    Kiran K.

    • Marked as answer by Budgie6059 Thursday, February 14, 2013 1:20 AM
    Wednesday, February 13, 2013 9:26 AM
  • Hi Tony,

    Macro needed some tweaking. I have have modified it to include Finish Time and also to execute it only for "Work Resources" and not for material or cost resources. 

    Sub ResourceExceptions()

    Dim MyXL As Object
    Set MyXL = CreateObject("Excel.Application")
    MyXL.Workbooks.Add
    MyXL.Visible = True

    MyXL.ActiveWorkbook.worksheets.Add.Name = "Exception Report"
    MyXL.ActiveWorkbook.worksheets("Exception Report").Activate

    Set xlRange = MyXL.activesheet.Range("A1")

    xlRange.Range("A1") = "Resource Name"
    xlRange.Range("B1") = "Start Time"
    xlRange.Range("C1") = "Finish Time"
    i = 2
        For Each R In ActiveProject.Resources
            If Not (R Is Nothing) Then
            ctr = 1
            If R.Type = pjResourceTypeWork Then
            If R.Calendar.Exceptions.Count > 0 Then
            For Each E In R.Calendar.Exceptions
            xlRange.Range("A" & i) = R.Name
            xlRange.Range("b" & i) = R.Calendar.Exceptions.Item(ctr).Start
            xlRange.Range("c" & i) = R.Calendar.Exceptions.Item(ctr).Finish
            ctr = ctr + 1
            i = i + 1
            Next
            End If
            End If
            End If
        Next R
    End Sub

    • Marked as answer by Budgie6059 Sunday, February 17, 2013 8:01 AM
    Sunday, February 17, 2013 7:59 AM

All replies

  • Hi Tony,

    Team planner view shows the "Non Working time" for resources. Select View >> Team Planner

    Normally it shows non working time in "red". You can customize it by right clicking in Time scale >> Non Working time.

    Thanks,

    Kiran K.

    • Marked as answer by Budgie6059 Thursday, February 14, 2013 1:20 AM
    Wednesday, February 13, 2013 9:26 AM
  • Tony --
     
    Unfortunately, the new Reports feature in Project 2013 does not allow you to create a resource calendar report to show nonworking time the way we could do that using the old Reports feature in Project 2010 and earlier.  Lacking that functionality, I like the suggestion from Kiran to see the periods of nonworking time using the Team Planner view.  This assumes, of course, that you are using the Professional version of Project 2013, since the Team Planner view is not available in the Standard version.  Hope this helps.
     

    Dale A. Howard [MVP]
    VP of Educational Services
    msProjectExperts
    http://www.msprojectexperts.com
    http://www.projectserverexperts.com
    "We write the books on Project Server"

    Wednesday, February 13, 2013 1:38 PM
    Moderator
  • Hi guys, thanks for your assistance.

    Kiran, I tried the method you suggested and can see the info I need (thanks :-).  If I may ask a quick follow up on this method though. Is there anyway within the Team Planner view to:

    1. remove the 'task/activities' from being visible?  i.e. at present in the calendar window (right pane) the resource lines include activities
    2. hide the 'standard calendars' non-working times, i.e. Weekends, public holidays etc that apply to all resources etc?

    cheers Tony

    Wednesday, February 13, 2013 11:47 PM
  • Tony --
     
    The answer to both questions is no.  Sorry.  Hope this helps.

    Dale A. Howard [MVP]
    VP of Educational Services
    msProjectExperts
    http://www.msprojectexperts.com
    http://www.projectserverexperts.com
    "We write the books on Project Server"

    Thursday, February 14, 2013 12:21 AM
    Moderator
  • Thanks Dale.  I'll live with what I can do then.

    cheers Tony

    Thursday, February 14, 2013 1:20 AM
  • Hi Tony,

    I have created a quick macro to export non working time from MS Project to excel. Use below steps

    Open the Project File

    Press Alt + F 11

    Right click on VBAProject(ProjectName) and select add Module

    Copy the below code in the module

    ==============

    Sub ResourceExceptions()

    Dim MyXL As Object
    Set MyXL = CreateObject("Excel.Application")
    MyXL.Workbooks.Add
    MyXL.Visible = True

    MyXL.ActiveWorkbook.worksheets.Add.Name = "Exception Report"
    MyXL.ActiveWorkbook.worksheets("Exception Report").Activate

    Set xlRange = MyXL.activesheet.Range("A1")

    xlRange.Range("A1") = "Resource Name"
    xlRange.Range("B1") = "Non Working Time"
    i = 2
        For Each R In ActiveProject.Resources
            If Not (R Is Nothing) Then
            ctr = 1
            If R.Calendar.Exceptions.Count > 0 Then
            For Each E In R.Calendar.Exceptions
            xlRange.Range("A" & i) = R.Name
            xlRange.Range("b" & i) = R.Calendar.Exceptions.Item(ctr).Start
            ctr = ctr + 1
            i = i + 1
            Next
            End If
            End If
        Next R
    End Sub

    ================== 

    You can run this macro using view >> Macro >> view macro >> select ResourceExceptions and click run

    To make this macro available for all project copy this macro in global.mpt. File >> Info >> Organizer >> Module >> Select "ResourceExceptions" from Project to global.mpt


    Thanks,

    Kiran K.

    Friday, February 15, 2013 2:59 PM
  • Hi Kiran,

    this kind of report is exactly what I was after, thanks for assisting however when I run the report I only seem to get the 'start date' of the unavailable time.  i.e. the report doesn't show the 'start' and 'finish' dates of the unavailable time.  Hopefully I've implemented your macro right (everything seemed to go in ok).. 

    As an example, in my project I've set my own (for me as the 'resource') unavailable time as follows:

    You can see I'm away from 22/4 through to 3/5 however when I run the report I see that I'm listed in it but only with the 'start' date, snippet of report output follows:

    Is this due to something I've done in adding the macro (i.e. does the macro work like this for you also) OR does the macro need tweaking to show the 'finish' date (and if so, would you mind tweaking it :-)...

    Cheers Tony

    Sunday, February 17, 2013 7:27 AM
  • Hi Tony,

    Macro needed some tweaking. I have have modified it to include Finish Time and also to execute it only for "Work Resources" and not for material or cost resources. 

    Sub ResourceExceptions()

    Dim MyXL As Object
    Set MyXL = CreateObject("Excel.Application")
    MyXL.Workbooks.Add
    MyXL.Visible = True

    MyXL.ActiveWorkbook.worksheets.Add.Name = "Exception Report"
    MyXL.ActiveWorkbook.worksheets("Exception Report").Activate

    Set xlRange = MyXL.activesheet.Range("A1")

    xlRange.Range("A1") = "Resource Name"
    xlRange.Range("B1") = "Start Time"
    xlRange.Range("C1") = "Finish Time"
    i = 2
        For Each R In ActiveProject.Resources
            If Not (R Is Nothing) Then
            ctr = 1
            If R.Type = pjResourceTypeWork Then
            If R.Calendar.Exceptions.Count > 0 Then
            For Each E In R.Calendar.Exceptions
            xlRange.Range("A" & i) = R.Name
            xlRange.Range("b" & i) = R.Calendar.Exceptions.Item(ctr).Start
            xlRange.Range("c" & i) = R.Calendar.Exceptions.Item(ctr).Finish
            ctr = ctr + 1
            i = i + 1
            Next
            End If
            End If
            End If
        Next R
    End Sub

    • Marked as answer by Budgie6059 Sunday, February 17, 2013 8:01 AM
    Sunday, February 17, 2013 7:59 AM
  • You're a legend, that's perfect!

    Thanks heaps for your help.

    cheers Tony

    Sunday, February 17, 2013 8:01 AM