none
Resource Calendar Exceptions - View in Project calendar RRS feed

  • Question

  • Hi,

    I am new to Microsoft Project and I am trying to use it to manage resources, particularly in terms of their availability (based on vacation/planned leave etc) for each task assigned. I have used the "change working time" to update all the resources' planned calendar exceptions.

    In the calendar view, I would like to see this displayed - for example this week, two members Ben and Pooja are away and I would like to see this in the calendar along with all manually scheduled tasks that the team is working on in this week.

    Please advise how I can edit the calendar so I can view this? It will be really helpful for our weekly team catch ups to see who is in/out and what needs to be done this week.

    Thanks

    Sarvee

    Monday, May 2, 2016 1:47 AM

All replies

  • Calendar exceptions are the right way for non-working times. However they are not tasks so they won't appear in the Calendar. The calendar is not a useful view as only a very few concurrent tasks can be shown.

    Don't use Manual tasks, use Auto scheduled tasks and link them.


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Monday, May 2, 2016 10:26 PM
    Moderator
  • Sarvee,

    I agree with Rod and his response made me remember something. Several months ago I answered a user post concerning a way to get a calendar exception report. I can't find the thread (the search feature on these forums is terrible) but I do have the VBA macro code that reads all the resource calendar exceptions and then exports that data to Excel.

    Hope this helps.

    John

    Option Explicit
    Sub CalendarExceptions()
    'Basic macro code created by Kiran.K and posted on MSDN Project
    ' customizing and programming forum Feb 7,2013
    'Code streamlined and updated by John - Project June 2,2014
    'Modified to pick up exceptions for all base calendars in a given Project file by John - Project Feb 8, 2016

    Dim MyXL As Object
    Set MyXL = CreateObject("Excel.Application")
    Dim i As Integer, j As Integer
    Dim BC As Calendar
    Dim E As Exception
    Dim r As Resource
    Dim xlRng As Range

    'open Excel, define workbook, and set column headers
    MyXL.Workbooks.Add
    MyXL.Visible = True
    MyXL.ActiveWorkbook.Worksheets.Add.Name = "Exception Report"
    MyXL.ActiveWorkbook.Worksheets("Exception Report").Activate
    Set xlRng = MyXL.ActiveSheet.Range("A1")
    xlRng.Range("A1") = "Proj Cal Holidays"
    xlRng.Range("B1") = "Base Calendar"
    xlRng.Range("C1") = "Start Date"
    xlRng.Range("D1") = "Finish Date"
    xlRng.Range("F1") = "Res Name"
    xlRng.Range("G1") = "Res Base Cal"
    xlRng.Range("H1") = "Base Cal Excep"
    xlRng.Range("I1") = "Start Date"
    xlRng.Range("J1") = "Finish Date"
    xlRng.Range("L1") = "Resource Name"
    xlRng.Range("M1") = "Res Excep"
    xlRng.Range("N1") = "Start Date"
    xlRng.Range("O1") = "Finish Date"

    'First gather and export Project calendar exceptions
    For Each BC In ActiveProject.BaseCalendars

    i = 2
    If BC.Exceptions.count > 0 Then
        For Each E In BC.Exceptions
            xlRng.Range("A" & i) = E.Name
            xlRng.Range("B" & i) = BC.Name
            xlRng.Range("C" & i) = E.Start
            xlRng.Range("D" & i) = E.Finish
            i = i + 1
        Next
    End If

    'Next, gather and export resource base calendar exceptions along with
    '   resource calendar exceptions
    i = 2
    For Each r In ActiveProject.Resources
        If Not r Is Nothing Then
            j = i
            If r.Type = pjResourceTypeWork Then
                    For Each E In r.Calendar.BaseCalendar.Exceptions
                        xlRng.Range("F" & i) = r.Name
                        xlRng.Range("G" & i) = r.Calendar.BaseCalendar.Name
                        xlRng.Range("H" & i) = E.Name
                        xlRng.Range("I" & i) = E.Start
                        xlRng.Range("J" & i) = E.Finish
                        i = i + 1
                    Next E
                    For Each E In r.Calendar.Exceptions
                        xlRng.Range("L" & j) = r.Name
                        xlRng.Range("M" & j) = E.Name
                        xlRng.Range("N" & j) = E.Start
                        xlRng.Range("O" & j) = E.Finish
                        j = j + 1
                    Next E
            End If
        End If
    Next r
    MyXL.ActiveWorkbook.Worksheets("Exception Report").Columns("A:N").AutoFit
    End Sub

    Sub BaseCalendarExceptions()
    'This macro exports the exceptions for all base calendars in a given Project file
    '(modified version of the CalendarExceptions macro published previously in this forum)
    ' Written by John - Project 2/8/16
    Dim MyXL As Object
    Set MyXL = CreateObject("Excel.Application")
    Dim i As Integer, j As Integer
    Dim BC As Calendar
    Dim E As Exception
    Dim xlRng As Range

    'open Excel, define workbook, and set column headers
    MyXL.Workbooks.Add
    MyXL.Visible = True
    MyXL.ActiveWorkbook.Worksheets.Add.Name = "BaseCal Exc Report"
    MyXL.ActiveWorkbook.Worksheets("BaseCal Exc Report").Activate
    Set xlRng = MyXL.ActiveSheet.Range("A1")
    xlRng.Range("A1") = "Proj Cal Holidays"
    xlRng.Range("B1") = "Base Calendar"
    xlRng.Range("C1") = "Start Date"
    xlRng.Range("D1") = "Finish Date"

    'Gather and export Project calendar exceptions
    j = 0
    For Each BC In ActiveProject.BaseCalendars
        i = 2 + j
        If BC.Exceptions.count > 0 Then
            For Each E In BC.Exceptions
                xlRng.Range("A" & i) = E.Name
                xlRng.Range("B" & i) = BC.Name
                xlRng.Range("C" & i) = E.Start
                xlRng.Range("D" & i) = E.Finish
                i = i + 1
            Next E
        End If
        j = i
    Next BC
    MyXL.ActiveWorkbook.Worksheets("BaseCal Exc Report").Columns("A:D").AutoFit

    End Sub

    Tuesday, May 3, 2016 12:06 AM
  • Thanks everyone. I don't know how to use VBA unfortunately...
    Tuesday, May 24, 2016 11:40 PM