I am trying to display over allocated resources and the specific date and hours of the over allocation in a separate View.
Listed below is a scenario to illustrate my inquiry…
For Project A, with Tasks T1 and T2 and Resources RNOT and R2 (sorry couldn’t resist the pun) and a four day duration from 1/2 to 1/5 with the following schedule:
1/2 1/3 1/4 1/5
T1 RNOT 7hrs 6hrs 6hrs 5hrs
T2 RNOT 0hrs 3hrs 2hrs 5hrs
What I would like to see is something along the lines of
Resource Date Over Allocated Hours Over Allocated
RNOT 1/3 1hr
R2 1/4 4hrs
Any assistance would be greatly appreciated.
Have a blessed day –
I believe it is not possible to create such a custom view. I have once used the following VBA routine to list overallocated hours in an Excel sheet.
Dim tsvs As TimeScaleValues
Dim i As Integer
Dim ResCount As Integer
Dim Rows As Integer
Dim XLApp As New Excel.Application
Dim Wbook As Workbook
Set Wbook = XLApp.Workbooks.Open(ActiveProject.Path + “OverAll.xls”)
Wbook.Sheets(“Sheet1”).Cells(1, 1).Value = “Overallocated Resource Name”
Wbook.Sheets(“Sheet1”).Cells(1, 2).Value = “Date Overallocated”
Wbook.Sheets(“Sheet1”).Cells(1, 3).Value = “Hours Overallocated”
Rows = 2
For ResCount = 1 To ActiveProject.NumberOfResources
If ActiveProject.Resources(ResCount).Overallocated Then
Wbook.Sheets(“Sheet1”).Cells(Rows, 1).Value = ActiveProject.Resources(ResCount).Name
Rows = Rows + 1
Set tsvs = _
For i = 1 To tsvs.Count
If Not tsvs(i).Value = “” Then
Wbook.Sheets(“Sheet1”).Cells(Rows, 2).Value = CDate(tsvs(i).StartDate)
Wbook.Sheets(“Sheet1”).Cells(Rows, 3).Value = tsvs(i).Value / 60
Wbook.Sheets(“Sheet1”).Cells(Rows, 4).Value = “hrs”
Rows = Rows + 1
Wbook.Close savechanges = yes
You need to create an empty Excel sheet named “OverAll.xls” in the same directory as the mpp file before running the macro. Click Alt + F11 to open VBE and copy the code above into a module and make sure “Microsoft Excel 1x.0 Object Library” checkbox is turned on ( [x] ) in References dialog box (Tools | References…). I did not have a chance to improve it. Pls consider testing it in a backup of your project plan and post for feedback. I hope it works.
Have you tried using the Overallocated usage detail as a means of indicating overallocation? Go to the usage view and hide subtasks (just show resources). Select the Overallocated usage style (you can right click the detail style column or choose overallocation from the format menu. That will show you hours overallocated for each resource.
Hope that helps…Larry
Or use the resource usage view, filter for overallocated resources, and in the timephased view add in Overallocated in the detail, removing work.
As an alternative, you can use the Resource Usage report. It will give you the totals as well.
In the code above, you can replace pjTimescaleDays with one of the other unit enumerations: http://msdn.microsoft.com/en-us/library/office/bb223241(v=office.12).aspx. And note that both the code and the report can be customized for a date range and a period.
Ismet | www.ismetkocaman.com